Hi,
In a dataset have a column named statatum which contains unique values 0 ,1 ,3 but in repetition.
I would like to create multiple different columns in this dataset such as YAS YES YCS sep, but for all hte unique values. such as YAS0 YES0 YCS0 SEP0 YAS1 YES1 YCS1 SEP1 YAS3 YES3 YCS3 SEP3 .
I require help to create these variables in form of a macro, so that if the unique value changes to 2,3,4 or anything X Y Z it must create the columns similar way.
What do you actually have? What do you actually want?
So the suffix 0,1,3 is coming from the variable STRATA.
Where is the prefix YAS, YES, YCS coming from? Is that in another variable?
What value do you want the new variables to have? Is that in another variable?
To just create the structure you could do something like this. First make a TALL dataset . Then TRANSPOSE it.
data tall;
row=1;
value=1;
do prefix='YAS','YES','YCS','SEP';
do suffix=0,1,3;
output;
end;
end;
run;
proc transpose data=tall out=wide(drop=_name_);
by row;
id prefix suffix;
var value;
run;
There are many places in this forum where experts advise against creating wide data sets with data in the column name, such as putting the zero value into YAS0, as these become difficult to analyze, and are not dynamic. Leaving the data in its original format not only makes the subsequent code easier to write, but avoids the problem that you state as "so that if the unique value changes to 2,3,4 or anything X Y Z it must create the columns similar way." And because we avoid this problem, a macro is normally not needed.
So ... my advice is to restructure the problem so you don't need a wide data set with data in the variable name.
If you explain more about what you plan to do with this data, we could definitely be more specific.
As far as I can see, survival plots are much more easily done on long data sets rather than wide data sets. So again, I urge you to not proceed with your plan as stated and reformulate the problem to use a long data set.
@sahoositaram555 wrote:
I do agree! but he scenario is not only alone on survival plot, few modifications in the survival plot for which i have crated a template. if you please could look below to the code by which i'm trying to achieve the code and help me to automate,I would be grateful.
%macro hhh();
%do i =%scan() %to &n_categories; **n_categories has all my unique values(0,1,3) sepated by space
proc transpose data= have;
id &&var&i.;
var statatum ;
run;
%end;
%mend;
%hhh();
From this, there is not enough information to know what you are doing or why you are doing this or where it is going. And so I will not comment on specific code at this time. I also point out that you continually arrive at a macro solution, and I simply am not in agreement.
To determine the best structure of the data and a reasonable approach, the details must be provided about this survival plot and template, and I think we'd also need to see a portion of the data.
What do you actually have? What do you actually want?
So the suffix 0,1,3 is coming from the variable STRATA.
Where is the prefix YAS, YES, YCS coming from? Is that in another variable?
What value do you want the new variables to have? Is that in another variable?
To just create the structure you could do something like this. First make a TALL dataset . Then TRANSPOSE it.
data tall;
row=1;
value=1;
do prefix='YAS','YES','YCS','SEP';
do suffix=0,1,3;
output;
end;
end;
run;
proc transpose data=tall out=wide(drop=_name_);
by row;
id prefix suffix;
var value;
run;
If you have a macro variable like:
%let strata = 0,1,3 ;
Then just use that in your DO statement.
do suffix=&strata ;
Or make a dataset with one observation per value.
proc sort data=have (keep=strata) out=suffix nodupkey;
by strata;
run;
And use the dataset instead of the DO loop.
data tall;
set suffix ;
do prefix='YAS',.....;
... calculate something here ...
output;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.