Search code examples
sassas-macrodatastep

Dynamically determine columns to search in datastep SAS

I'm new to SAS, and need some assistance with a very basic problem.

  • I have hundreds of individual datasets and I want to extract rows that match a certain diagnostic ID.
  • The diagnostic ID's are listed in columns DX1-DX11. However some datasets have only DX1-DX4, and others have diagnostic IDs within DX1-DX09.
  • So I need a way to determine the number of DX columns in a particular dataset, so that I can match my diagnostic ID's of interest to the ID's presented in the dataset across all appropriate columns.

For example, I wanted to do the following, but I cannot do this across all datasets because if a dataset does not have column DX11 for example, the code breaks, since DX11 is not present in the dataset. 

DATA DX_CODES (KEEP = ID DX1 DX2 DX3 DX4 DX5 DX6 DX7 DX8 DX9 DX10 DX11);
                                    SET LIB.&SAS_FILE_N;    
                                        WHERE DX1 IN ('123', '1234', '12345', '123456') OR
                                        DX2 IN ('123', '1234', '12345', '123456') OR
                                        DX3 IN ('123', '1234', '12345', '123456') OR 
                                        DX4 IN ('123', '1234', '12345', '123456') OR
                                        DX5 IN ('123', '1234', '12345', '123456') OR
                                        DX6 IN ('123', '1234', '12345', '123456') OR
                                        DX7 IN ('123', '1234', '12345', '123456') OR
                                        DX8 IN ('123', '1234', '12345', '123456') OR
                                        DX9 IN ('123', '1234', '12345', '123456') OR
                                        DX10 IN ('123', '1234', '12345', '123456') OR
                                        DX11 IN ('123', '1234', '12345', '123456');
                                    RUN;
Solution

  • Use variable short cut lists instead and restructure your code. DX: will refer to all variable starting with DX. 

    IF whichc('123', of dx:) or whichc('1234', of dx:)...

    You need to use IF instead of WHERE because WHERE does not support the use of short cuts.

    Long term, it's faster and easier to work with your data in a long format rather than a wide format then it also doesn't matter where the diagnosis is in the list of diagnoses.