topic Re: Macro to transverse list of variable names from dictionary.columns, then perform action on data in SAS Programming

Macro to transverse list of variable names from dictionary.columns, then perform action on data set

Jumboshrimps — Fri, 19 May 2023 21:58:43 GMT

Creating a macro to first select all column_names from a data set into a list "dcodes",

then cycle through column_names, and if the column_name starts with "Diagnosiscode" (three such columns - Diagnosiscode1, Diagnosiscode2, Diagnosiscode3) apply simple logic based on the CONTENTS of the variable. If the contents of var Diagnosiscode1 is between the string "DC_101" and "DX_900" then perform some action (add a flag to another, different variable not in the :dcodes list - ("DX_GP01").

The log below clearly shows macro variable DIAG resolves to diagnosiscode1, yet the mlogic resolves to false.

When DIAG resolves to "Diagnosiscode1", or "Diagnosiscode2" or "Diagnosiscode3", the next step will fail -line 11 - (logically), as the string "Diagnosiscode1" is of course, NOT

ge 'DX_515' and le 'DX_910'. What I want is to assign DIAG to the CONTENTS of the variable "Diagnosiscode1" in data set Medical, so the logic would work if the data in that variable was in fact,

ge 'DX_515' and le 'DX_910'.

So there are two issues:

1. Why doesn't the logic in line 10 work the attached log.

2. Once the logic does work, how to change macro variable DIAG from line 8 so the CONTENTS of the name of that variable can be scanned for certain conditions, and if true, apply the logic to another variable.

A simpler way would be to just loop through the variables Diagnosiscode1, Diagnosiscode2 & Diagnosiscode3, one row at a time, and modify the contents of a second variable based on what was found.

There are many examples of looping through a SAS data set here, but the the output is always to another data set. I want to change values in the same data set.

THX.


1 %macro sum_flg();
2 data med_grp;
3 length dx_GP01 2.;
4 set medical;
5 %local i diag;
6 %let i=1;
7 %do %while (%scan(&dcodes, &i) ne );
8 %let diag = %scan(&dcodes, &i);
9 %put &diag.;
10 %if &diag. = ('diagnosiscode%') %then %do;
11 %if &diag. ge 'DX_515' and &diag. LE 'DX_910' %then %let Dx_GP01 = '1';
12 %ELSE Dx_GP01 = '0';
13 %end;
14 %let i = %eval(&i + 1);
15 %end;
16 %mend sum_flg

SYMBOLGEN:  Macro variable DIAG resolves to diagnosiscode1
MLOGIC(SUM_FLG):  %IF condition &diag. = ('diagnosiscode%') is FALSE

Re: Macro to transverse list of variable names from dictionary.columns, then perform action on data

Tom — Fri, 19 May 2023 22:18:31 GMT

What SAS code do you want to use the macro to generate?

First step is to write the code you want it to generate and then you can start to plan how you can have the macro generate it.

For example you said:

What I want is to assign DIAG to the CONTENTS of the variable "Diagnosiscode1" in data set Medical,

Does that mean you want to assign the actual SAS variable named DIAG to the value of the actual SAS variable named DIAGNOSISCODE1 ? The SAS code for that would look like this:

diag = diagnosiscode1 ;

So if you had the string diagnosiscode1 in the macro variable named DIAGVAR you could use it to generate that code in this way.

diag = &diagvar ;

Of course you would have to place that assignment statement inside of a data step. So if DIAGNOSISCODE1 is a variable in the dataset MEDICAL the SAS code might look like:

data want;
  set medical;
  diag = &diagvar ;
....

Re: Macro to transverse list of variable names from dictionary.columns, then perform action on data

Tom — Sat, 20 May 2023 01:23:04 GMT

This problem:

if the column_name starts with "Diagnosiscode" (three such columns - Diagnosiscode1, Diagnosiscode2, Diagnosiscode3) apply simple logic based on the CONTENTS of the variable. If the contents of var Diagnosiscode1 is between the string "DC_101" and "DX_900" then perform some action (add a flag to another, different variable not in the :dcodes list - ("DX_GP01").

Does not need a macro.

data want;
  set have;
  array dxcodes diagnosiscode: ;
  dx_gp01 = 0;
  do index=1 to dim(dxcodes) until(dx_gp01);
    dx_gp01 = 'DC-101' <= dxcodes[index] <= 'DX_900' ;
  end;
run;

Re: Macro to transverse list of variable names from dictionary.columns, then perform action on data

SASKiwi — Sat, 20 May 2023 04:28:20 GMT

This is a classic example of where a long data table (diagnoses in rows) is way better than your current wide table (diagnoses in columns) and will make your processing a lot easier. If you want to try this than please provide us with some sample data using the DATA step DATALINES statement.