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
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.
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
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;
it works , thank you
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
@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?
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_.
it works, thank you!
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.