topic Re: create multiple columns based on a existing variable in a macro in SAS Programming

create multiple columns based on a existing variable in a macro

sahoositaram555 — Thu, 23 Jul 2020 10:47:41 GMT

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.

Re: create multiple columns based on a existing variable in a macro

PaigeMiller — Thu, 23 Jul 2020 11:04:11 GMT

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.

Re: create multiple columns based on a existing variable in a macro

sahoositaram555 — Thu, 23 Jul 2020 11:12:54 GMT

Thanks @PaigeMiller, sole intention of creating large dataset is to create use them as arguments in my proc template procedure. my intention is to create a survival plot where all these 3 types are 3 different strata. For each strata types, i need to use YAS &YCS in layout overlay as x & y, while others to be used in markercharacter options of proc template.
,

Re: create multiple columns based on a existing variable in a macro

PaigeMiller — Thu, 23 Jul 2020 12:58:15 GMT

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.

Re: create multiple columns based on a existing variable in a macro

sahoositaram555 — Thu, 23 Jul 2020 14:07:58 GMT

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();

Re: create multiple columns based on a existing variable in a macro

Tom — Thu, 23 Jul 2020 14:22:29 GMT

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;

Re: create multiple columns based on a existing variable in a macro

sahoositaram555 — Thu, 23 Jul 2020 14:33:34 GMT

Thanks @Tom! to answer your all qustions: A) suffix 0,1,3 comes from the column statatum from HAVE dataset . B) YAS','YES','YCS','SEP' are my own defined column names that i would like to create. C)Values i need to define conditionally, many if else conditions based on these column names
The way you have created this is wonderful. Will save this. ONE ADVICE I NEED FURTHER ON THIS IS HOW TO AUTOMATE STEP IN YOUR CODE do suffix=0,1,3;
if this part is automated by passing 0 ,1 , 3 automated not by hard coding then at could use it for my proc template.

Re: create multiple columns based on a existing variable in a macro

Tom — Thu, 23 Jul 2020 14:48:32 GMT

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;

Re: create multiple columns based on a existing variable in a macro

PaigeMiller — Thu, 23 Jul 2020 15:09:36 GMT

@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.

Re: create multiple columns based on a existing variable in a macro

sahoositaram555 — Thu, 23 Jul 2020 15:57:13 GMT

Thanks @Tom, i have took the distinct values by proc sql and stored them in a macro .Later with reference to your last post I've simply followed the proc transpose procedure to o the Job.

Thanks again for looking patiently to the query and helping me building the concept!