Create dynamic dummy variable

[jenim514]

I am working on dataset that integrated muliple populations- using macro to call each population.  I am having problems with creating a dynamic dummy variable that will change with each population.

I'm working on a table that needs to look like this- however there are all 'By Imputaion' values are null, so I created a dummy to output that 'Imputation' line.  Problem is, it needs to output at every 'Preferred term' ...and this total count differs by population.

So here-  where tord=1,  is 'By Attribution', and tord=2 is the dummy 'By Imputation'.   However, the total number of terms will change as I call the population in the macro.

/*manually create the line for imputations since all null*/  - I merged this skeleton file with the dataset to create the dummy, but the 'Count' will change in each population.  Is there a way do dynamically assign the 'count'  in a macro?
data skel;
do count=2 to 47;
do tord=0 to 2;
output;
end;
end;
run;

Thanks in advance!!

[PaigeMiller]

First step: write working SAS code that does what you want without macros and without macro variables for one input data set. It doesn't appear you have done that. This is critical to creating dynamic code using macros or macro variables or via other methods. If you don't have working code without macros and without macro variables, then it will never work with macros or with macro variables.

[jenim514]

@PaigeMiller hello, I have written the code that works for one population.   When I change to POP2 the 'SKEL' dataset no longer is sufficined becasue the 'count' variable witll change in each population.

%macro set (pop);
proc freq data = pre_data (where= (&pop= 1)) noprint;

tables TRT*AOCC01FL / out = ae (where = (AOCC01FL = "Y"));
tables TRT*AOCC03FL*AEDECOD / out = pt (where = (AOCC03FL = "Y"));
tables TRT*AOCC03FL*attrfl*AEDECOD / out = attype (where = (AOCC03FL = "Y" and attrfl='Y')); /*dummy flag for the table set up - attribution */
tables TRT*AOCC03FL*AOCC27FL*AEDECOD / out = imptype (where = (AOCC27FL = "Y" and AOCC03FL='Y' )); /*imputation*/
run;

data all;
set ae (in=a) pt (in=b) attype (in=c) imptype(in=d);

if b then tord=0;
if c then tord=1;
if d then tord=2;
proc sort; by AEDECOD; run;

/***ASSIGNS FINAL ORDER TO PREFERED TERM***/
proc sort data = pt out = pt_ord&pop (keep = AEDECOD COUNT rename = (COUNT = PTORD));
where TRT = 99;
by AEDECOD;
run;

proc sort data=all; by aedecod;run;
data all2&pop;
merge all (in=a) pt_ord&pop;
by AEDECOD;
if a;

if AEDECOD = "" then PTORD = 9999;
if PTORD = 9999 then TEXT = "Subjects Reporting at Least One TEAE";
else TEXT = strip(AEDECOD);
run;

proc sort; by trt; run;

proc sort data=bign&pop; by trt; run;
proc sort data=all2&pop; by trt; run;

data stats2;
length x1 $12;
merge bign&pop all2&pop (in=a rename=(count=res) DROP=PERCENT);
by trt;
if a;
if res ^=. and count^=. then x1=strip(put(res,best.))||" ("||strip(put(res/count*100, 10.1))||"%)";
else if res ^=. then x1=strip(put(res,best.));

proc sort; by descending PTORD AEDECOD TORD TEXT;
run;

proc transpose data = stats2 out = all_trans;
by descending PTORD AEDECOD tord TEXT;
id TRT;
var X1;
run;

proc sort data =all_trans; by descending PTORD AEDECOD tord TEXT;

/***CREATE COUNT VARIABLE TO MERGE WITH TORD***/
data trans2;
set all_trans ;
by text notsorted;
if first.text then count +1;
run;
proc sort; by count tord; run;

/*manually create the line for imputations since all null*/
data skel;
do count=2 to 47;
do tord=0 to 2;
output;
end;
end;
run;

data pre;
merge trans2 skel;
by count tord;
run;

%mend set;

%set (POP1);

[jenim514 · Accepted solution]

I resolved 🙂 just added this to the dummy and now it is working across populations

data show&pop;
set shellpop end=last;
if last then call symput('NumDummies', _n_);
run;

%put Total Number of Dummy Variables: &NumDummies;
/*manually create the line for imputations since all null*/
data skel;
do count=2 to &NumDummies;
do tord=0 to 2;
output;
end;
end;
run;

[mkeintz]

Great! - so mark your own response as the solution, (of @PaigeMiller's if the suggestion to solve a single-population case is the key).

That way viewer of the current topic list will know the status of your topic before opening it.