BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

Hi,

I am transposing a dataset with column names set to "week of 1/1/2015", "week of 8/1/2015" and so on. Now I need a column based filter in SAS VA 7.1 LASR dataset.

 

 week1week1week1week2week2week2
week1XXX   
week1XXX   
week1XXX   
week2   XXX
week2   XXX
week2   XXX

 

At worst, I will be stuck with emplty columns which is ok with me. Any ideas how do I get it done?

 

Thanks,

saspert

2 REPLIES 2
Jagadishkatam
Amethyst | Level 16
Could you please provide a sample data and the expected output to get a better solution

Thanks,
Jag
Thanks,
Jag
saspert
Pyrite | Level 9

Here is a crude attempt to transpose my dataset. Looking for better alternatives. The "crap" field is a fix I read in a SUGI paper while dealing with both numeric and character variables while transposing. I actually dont need the crap field but I have to put it because I dont want the formats to get lost while transposing. 

 


proc sql;
create table mthly_sumry as
select country,
product,
month,
/*sum( '$ SPENT'n) as sum_pln_spent, */
sum('Actual GRPs'n) as actual_grp format=comma20.,
sum('Actual IMPs'n) as actual_imp format=comma20.,
sum('Actual Spend'n) as actual_spend format=dollar20.2 ,
sum('Buy Now'n) as buynow,
/*sum('Cl. Spots'n) as sum_clspot,*/
/*sum(Coupons),*/
/*sum(Date Aired),*/
sum('Planned GRPs'n) as planned_grp format=comma20.,
sum('Planned IMPs'n) as pln_imp format=comma20.,
sum('Planned Spend'n) as pln_spend format=dollar20.2,
/*sum(Store),*/
/*sum(Test) ,*/
sum('VIS To BLU'n) as visits,
(calculated actual_spend)/(calculated pln_spend) as spend_clearance format=percent10.2,
(calculated actual_imp)/(calculated pln_imp) as imp_clearance format=percent10.2,
(calculated actual_spend)/( calculated visits) as cpv
/*sum(W25-54IMP),*/
/*sum(W25-54RTG)*/
from lasrlib.bb_may2015
group by country,product,month;
quit;

proc sql;
select
count(distinct month) as cntmth into :cnt_bb_dist_mth
from lasrlib.bb_may2015;
quit;
%let cnt_bb_dist_mth_trm=%CMPRES(&cnt_bb_dist_mth);
%put "the number of distinct weeks in compressed format: &cnt_bb_dist_mth.";
proc sql;
select distinct month as month into :bb_mths1-:bb_mths&cnt_bb_dist_mth_trm.
from lasrlib.bb_may2015
order by month
;
quit;
run;
%put "&bb_mths1-&&bb_mths&cnt_bb_dist_mth_trm.";


Data basedataset;
run;
%macro _loopthrumths;
%do i = 1 %to &cnt_bb_dist_mth_trm;
%put &&bb_mths&i.;
proc sql;
create table month&&bb_mths&i. as
select * from mthly_sumry
where upcase(month)=upcase("&&bb_mths&i.")
;
quit;
data tmp_month&&bb_mths&i.;
set month&&bb_mths&i.;
month_filter=month;
crap="crap";
run;
proc sort data= tmp_month&&bb_mths&i.;
by country product month_filter;
run;
proc transpose data=tmp_month&&bb_mths&i. out=mthly_smry_&i._tr (rename=(_name_=metric) ) ;
by country product month_filter;
id month;
var actual_grp
actual_imp
actual_spend
buynow
planned_grp
pln_imp
pln_spend
visits
crap
;
run;
data basedataset (drop=crap);
set basedataset
mthly_smry_&i._tr
;
if metric in ('crap'," ",'') then delete;
Metric=upcase(TRANWRD(metric,"_"," "));
run;
proc sql;
create view monthly_smry_final as
select * from basedataset
;
quit;

%end;
%mend _loopthrumths;
%_loopthrumths
/* Drop existing table */
%vdb_dt(LASRLIB.LSR_BBMAY2015_MTHSMRY_TR);
data LASRLIB.LSR_BBMAY2015_MTHSMRY_TR ( );
set monthly_smry_final ( );
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 879 views
  • 1 like
  • 2 in conversation