MACRO DO LOOP

[meckarthik]

Hi ,

I met with problem trying to add new columns for week from 2013 onwards. Eg. wk1 -2013....wk25-2019. Although i was able to obtain do loop for wk1 to wk53 dont know how to add wk1-2013 ..etc 

%macro sqlloop;
proc sql;
create table SMGCAMPAIGNS_week_Add as
select *,
case when myweek = 1 a then 1 else 0 end as wk1
%do n=2 %to 53;
,case when myweek = &n  then 1 else 0 end as wk&n
%end;

from SMGCAMPAIGNS_final;
quit;

%mend;
%sqlloop;

Is there way to add loop years from 2013 onwards and create columns accordingly 

[Kurt_Bremser]

You try to transpose your data. SAS provides proc transpose for this.

If you provide some example for the dataset SMGCAMPAIGNS_final, I can give you an example for the usage of transpose.

[meckarthik]

ID           Weeks    Years

681840   22            2019
682346  20            2019
682404  20           2019
677570  20          2019
677573  20         2019
681545  20        2019
681667  20       2019
681542  20      2019

[Kurt_Bremser]

See this code and play around with it:

data have;
input ID $ Weeks Years;
datalines;
681840  22 2019
682346  20 2019
682404  20 2019
677570  20 2019
677573  20 2019
681545  20 2019
681667  20 2019
681542  20 2019
;

data pretrans;
set have;
/* build the future column name */
idvar = 'period_' !! put(years,z4.) !! put(weeks,z2.);
/* set the value we later want to have */
dummy = 1;
output;
/* create a dummy template for all expected periods */
if _n_ = 1
then do;
  id = ' '; /* so it will be first after the sort */
  do years = 2013 to 2019;
    do weeks = 0 to 53;
      idvar = 'period_' !! put(years,z4.) !! put(weeks,z2.);
      output;
    end;
  end;
end;
run;

proc sort data=pretrans;
by id;
run;

proc transpose
  data=pretrans
  out=want (
    drop=_name_
    where=(id > " ")
    /* filters the dummy table */
  )
;
by id;
id idvar;
var dummy;
run;

[meckarthik]

it works , thank you 

[Astounding]

What is your ultimate plan here? If you provide a few details, there may be a way to get there without the need for macro language or hundreds of new variables.

[meckarthik]

want to create new columns based on weeks and year listed in my table.  if the weeks or year not exist then dont create the columns. For example week 1 - 2013 may be in data then create week1-2013 else don't. Assume got 5 years of dataset, not all months /weeks got the data.  

ID           Weeks Year

681840    22   2019
682346   20   2019
682404   20   2019
677570   20   2019
677573   20   2019
681545   20   2019
681667   20   2019
681542   20   2019

[ballardw]

---

@meckarthik wrote:

want to create new columns based on weeks and year listed in my table.  if the weeks or year not exist then dont create the columns. For example week 1 - 2013 may be in data then create week1-2013 else don't. Assume got 5 years of dataset, not all months /weeks got the data.  

ID           Weeks Year

681840    22   2019
682346   20   2019
682404   20   2019
677570   20   2019
677573   20   2019
681545   20   2019
681667   20   2019
681542   20   2019

---

What is the purpose for all of these columns? Do you actually need a data set or a report that people read? Data with changing numbers of columns is quite often a problem to manipulate.

Does your data already contain a variable with SAS date values?

[Quentin]

You can nest macro %DO loops, similar to how you can nest data step DO loops.  

Below example I added two parameters to the macro, which allow you to input the first year and the last year.  The macro then loops over the years, and within each year, loops over the week numbers.

%macro sqlloop
  (yearmin=
  ,yearmax=
  )
;
%local n year ;

%do year=&yearmin %to &yearmax ;
  %do n=2 %to 53;
    %put &n &year wk&n._&year ;
  %end;
%end ;

%mend;


%sqlloop(yearmin=2017,yearmax=2019)

Note that wk&n._&year resolves to wk2_17.  The dot after &n is needed to end the macro variable reference.  Otherwise the macro processor would look for a macro variable named n_.  

[meckarthik]

it  works, thank you!