topic Re: selecting codes within a range from 25 columns in SAS Programming

selecting codes within a range from 25 columns

lalohg — Thu, 15 Nov 2018 20:09:08 GMT

Hi there,

I have 25 variables with codes (some are missing) and would like to create new columns/variables within specific code range. For example the last column "DiseaseIwant" in the attached "test.xlsx" file has an entry "Yes" because at least one of the 25 columns was coded within the range that I selected, in this case from E10 to E14. (record 4 of Column15 is E102 and is highlighted in yellow therefore "DiseaseIwant" was entered "Yes" for record 4.

could you please help me with the SAS code to get column "DiseaseIwant"?

Thank you.

Re: selecting codes within a range from 25 columns

Reeza — Thu, 15 Nov 2018 20:15:14 GMT

Is E10-E14 your full list?
Or can that change? I'm assumign that anything STARTING with E10, E11, E12, E13, E14 would be valid, such as E105.4 or E140.8

Re: selecting codes within a range from 25 columns

Shmuel — Thu, 15 Nov 2018 21:10:48 GMT

Would you prefer to define a list of diseases instead of a range, just because your codes are alphanumeric,

then you can try next code:

%let codes = E10 E11 E12 ..... ;

data want;
  set have;
        array dx $ disease: ;
        do i=1 to dim(dx);
             if findw("&codes" , dx(i)) > 0 then DiseaseIwant = 'Yes;
             else DiseaseIwant = 'No';
        end;
run;

Re: selecting codes within a range from 25 columns

ballardw — Thu, 15 Nov 2018 21:11:45 GMT

Character comparisons in general do not work very well with a "range" of values such a < E14.

You should really provide a complete list of exact values that are the range. Also are you looking for a single similar set of values or do you have a large number? For example could your DiseaseIwant ,or some other varialble, also need some values that start with K, Q and Z?

Re: selecting codes within a range from 25 columns

lalohg — Fri, 16 Nov 2018 17:31:08 GMT

Thanks for your answer Ballardw,

Yes!, I need to create new variables from alphanumeric codes starting from A to Z but each new variable within a range, for example one new variable will have codes from "C00" to "C979", another one from "C250" to "C254" another column from "E10" to "E14", another column with just "E11" and so on...

some codes in the xlsx table that I attached have 3 or 4 characters, this was a mistake, all should have 4 characters, sorry about this

Also, I just found out that some of the 25 columns in the data sets (each data set with more tha 500,000 rows) where the new variables will be created from have more than one code (up to 5 or 6 codes) so will need a code to scan these extra codes in each of the 25 columns,

It "sounds" complicated and wonder if this can be done?

thank you very much.

Lalohg

Re: selecting codes within a range from 25 columns

Reeza — Fri, 16 Nov 2018 17:40:33 GMT

Do those mappings, which code goes to which group, exist in a data set somewhere? Or is on paper or in your head? If you put it into a dataset, a format is likely your best option.