column filter

saspert — Mon, 14 Sep 2015 16:56:52 GMT

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.

	week1	week1	week1	week2	week2	week2
week1	X	X	X
week1	X	X	X
week1	X	X	X
week2				X	X	X
week2				X	X	X
week2				X	X	X

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

Thanks,

saspert

Re: column filter

Jagadishkatam — Tue, 15 Sep 2015 01:07:18 GMT

Could you please provide a sample data and the expected output to get a better solution

Thanks,
Jag

Re: column filter

saspert — Tue, 22 Sep 2015 22:05:51 GMT

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;

topic Re: column filter in SAS Programming

column filter

Re: column filter

Re: column filter