How to create multiple variables with date suffix in a do loop

[lydiawawa]

Hi,

I'm trying to create multiple variables based on conditions with date as their suffix. The following code only generate one variable from the end date. How do I make it generate multiple variables based on dates in between the end and start date, and keep them on master2?

%macro dis_status(startdate,enddate);
%let sasstart = %sysfunc(inputn(%substr(&startdate,3,6),yymmdd6.));
%put &sasstart;
%let sasend = %sysfunc(inputn(%substr(&enddate,3,6),yymmdd6.));
%put &sasend;

%do sasdate=&sasstart %to &sasend;
	%let date= %sysfunc(putn(&sasdate,yymmdd6.));
	%put &date;

     data master2;
       set master1;
		if code22_&date. in ('1','2','3','4') 
				then status22_&date. = "G "; 
		else if CODE22_&date. in ('5','6') 
		                                 then status22_&date. = "IE"; 
		else if CODE22_&date. in ('7','8') 
				then status_&date. = "NR"; 
		else CODE22 in ('10,'11')
				then status_&date. = "UE"; 
       run;
%end;
%mend;

%dis_status(20220608,20221019);

Thanks!

Add Tags

[lydiawawa]

I made a typo in the code:

Following is the corrected version:

%macro dis_status(startdate,enddate);
%let sasstart = %sysfunc(inputn(%substr(&startdate,3,6),yymmdd6.));
%put &sasstart;
%let sasend = %sysfunc(inputn(%substr(&enddate,3,6),yymmdd6.));
%put &sasend;

%do sasdate=&sasstart %to &sasend;
	%let date= %sysfunc(putn(&sasdate,yymmdd6.));
	%put &date;

     data master2;
       set master1;
		if code22_&date. in ('1','2','3','4') 
				then status22_&date. = "G "; 
		else if CODE22_&date. in ('5','6') 
		                                 then status22_&date. = "IE"; 
		else if CODE22_&date. in ('7','8') 
				then status22_&date. = "NR"; 
		else CODE22 in ('10,'11')
				then status22_&date. = "UE"; 
       run;
%end;
%mend;

%dis_status(20220608,20221019);

[yabwon]

Why to use macro loop at all? Array seems to be better solution:

data master1;
length code22_1-code22_5 status22_1-status22_5 $ 2;
code22_1   = "1";
code22_2   = "5";
code22_3   = "7";
code22_4   = "10";
code22_5   = "12";
run;
title "Before";
proc print;
run;




data master2;
  set master1;

  array codeVariable code22_:;
  array statusVariable status22_:;
   
  DO OVER codeVariable;
    if codeVariable in ('1','2','3','4') 
    then statusVariable = "G "; 
    else if codeVariable in ('5','6') 
    then statusVariable = "IE"; 
    else if codeVariable in ('7','8') 
    then statusVariable = "NR"; 
    else if codeVariable in ('10','11')
    then statusVariable = "UE";
    else statusVariable = "XX"; /* for unknown cases */
  end;
run;
title "After";
proc print;
run;

Bart

[lydiawawa]

I need to have the suffix to be named after the dates within the range entered. Those variables will be passed to another macro and be called with the names: status&date.

[PaigeMiller]

---

@lydiawawa wrote:
I need to have the suffix to be named after the dates within the range entered. Those variables will be passed to another macro and be called with the names: status&date.

---

Agreeing with @ballardw and @Kurt_Bremser — your data is poorly structured and this is causing you problems. Calendar dates (and any data) do not  belong in variable names. It is usually much easier to work with data in a different structure. Please take a step back, and explain the big picture of this project — what the goals are and what output analyses/reports you need, and not that you need certain pieces of code to do something — and then we ought to be able to provide a better data structure which results in easier programming.

[Kurt_Bremser]

Your dataset layout causes your problems. Do not keep data (dates) in structure (variable names).

Avoid transposing to a wide layout while you process data, do this only at the end when creating reports for human consumption or when certain procedures (regression) need it.

If you receive data in a wide layout, transpose to long as a first step. For help with this, post an example of your master1 dataset.

Instead of the IF/THEN/ELSE IF chain create a format and use a single PUT function for the conversion. Adding a line to the PROC FORMAT code is less work than adding another IF statement.

[Reeza · Accepted solution]

This is probably closer to what you're attempting to do, making some assumptions:

Some things to consider:

• You were using yymmdd6 but your dates appear to have 8 digits? Not sure your dates were being correctly derived.
• At one point you reference code22 not code22_&date? Is that a typo?
• data master2; set master1. Every time you loop you replace it with the original data set, and any previous changes are lost so only the last results would be retained.
• Assumes your variables are code22_YYYYMMDD and status22_YYYYMMDD. If they have a different structure you need to modify how the variable lists are being created. 
• EDIT: Changed as I assume your variables are actually codeYYMMDD and statusYYMMDD not code22_ and status_ ...will leave further changes up to you.

As @yabwon suggested, arrays are a better option here. If you have require the date in the name for whatever reason, then dynamically generate it. 

Code is untested as there is no sample data.

%macro dis_status(startdate,enddate);

data date_list;
do date = input("&startdate", yymmdd8.) to input("&enddate", yymmdd8.);
code_list = catt('code', put(date, yymmddn6.));
status_list = catt('status', put(date, yymmddn6.));
output;
end;
run;

*create list of variables for array;
proc sql noprint;
select code_list into :code_list separated by " " from date_list;
select status_list into :status_list separated by " " from date_list;
quit;



     data master2;
       set master1;
       
        array _code(*) &code_list.;
        array _status(*) &status_list.;
		do i=1 to dim(_code);
		if _code(i) in ('1','2','3','4') 
				then _status(i) = "G "; 
		else if _code(i) in ('5','6') 
		                                 then _status(i) = "IE"; 
		else if _code(i) in ('7','8') 
				then _status(i) = "NR"; 
		else _code(i) in ('10', '11')
				then _status(i) = "UE"; 
	   end;
       run;

%mend;

%dis_status(20220608, 20221019);

Spoiler

---

@lydiawawa wrote:

I made a typo in the code:

Following is the corrected version:

%macro dis_status(startdate,enddate);
%let sasstart = %sysfunc(inputn(%substr(&startdate,3,6),yymmdd6.));
%put &sasstart;
%let sasend = %sysfunc(inputn(%substr(&enddate,3,6),yymmdd6.));
%put &sasend;

%do sasdate=&sasstart %to &sasend;
	%let date= %sysfunc(putn(&sasdate,yymmdd6.));
	%put &date;

     data master2;
       set master1;
		if code22_&date. in ('1','2','3','4') 
				then status22_&date. = "G "; 
		else if CODE22_&date. in ('5','6') 
		                                 then status22_&date. = "IE"; 
		else if CODE22_&date. in ('7','8') 
				then status22_&date. = "NR"; 
		else CODE22 in ('10,'11')
				then status22_&date. = "UE"; 
       run;
%end;
%mend;

%dis_status(20220608,20221019);

---

[ballardw]

First, you are complicating things using that %substr in the input. Just convert the whole value.

%let sasstart = %sysfunc(inputn(&startdate,yymmdd8.));
%put &sasstart;
%let sasend = %sysfunc(inputn(&enddate,yymmdd8.));
%put &sasend;

You aren't very clear about what you want. But I think the straight translation of what I think you meant to attempt is to place the MACRO DO loop inside the data step. Otherwise you were overwriting the same data set multiple times and only getting the value of the enddate as a result.

data master2;
   set master1;

   %do sasdate=&sasstart %to &sasend;
	   %let date= %sysfunc(putn(&sasdate,yymmdd6.));

      if code22_&date. in ('1','2','3','4') 
      then status22_&date. = "G "; 
      else if CODE22_&date. in ('5','6') 
      then status22_&date. = "IE"; 
      else if CODE22_&date. in ('7','8') 
      then status_&date. = "NR"; 
      else CODE22 in ('10','11')
      then status_&date. = "UE"; 
   %end;
   run;

Addition: Is this line

 else CODE22 in ('10','11')

supposed to be as in the first 3 comparisons.

 else CODE22_&date. in ('10','11')

Do note that you were missing a ' around the  10 in both versions of the code.

In general placing data into the variable names, such as these data  suffixes is often an indication of poor data structure for programming in SAS and tends to reflect spreadsheet thinking. For most analysis or reporting having each date on a separate observation with a date named and valued variable to makes the group leads to much simpler coding, analysis and reporting.