topic Re: MACRO DO LOOP in New SAS User

MACRO DO LOOP

meckarthik — Wed, 22 May 2019 11:22:12 GMT

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

Re: MACRO DO LOOP

Kurt_Bremser — Wed, 22 May 2019 11:36:27 GMT

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.

Re: MACRO DO LOOP

Astounding — Wed, 22 May 2019 12:02:28 GMT

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.

Re: MACRO DO LOOP

meckarthik — Wed, 22 May 2019 12:07:11 GMT

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

Re: MACRO DO LOOP

meckarthik — Wed, 22 May 2019 12:10:34 GMT

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

Re: MACRO DO LOOP

Kurt_Bremser — Wed, 22 May 2019 12:24:10 GMT

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;

Re: MACRO DO LOOP

Quentin — Wed, 22 May 2019 12:36:21 GMT

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

Re: MACRO DO LOOP

ballardw — Wed, 22 May 2019 14:51:56 GMT

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

Re: MACRO DO LOOP

meckarthik — Thu, 23 May 2019 08:26:49 GMT

it works , thank you

Re: MACRO DO LOOP

meckarthik — Thu, 23 May 2019 08:27:18 GMT

it works, thank you!