Hey Tom,
Here is my actual code it may help you to understand..
/********* Importing Raw data ********************************************************/
proc import out = master
datafile = "c:\temp\file.txt"
dbms = dlm replace;
delimiter=';';
getnames = no;
run;
Data want(
rename=
(Var1=Client_Number Var2=Report_Number Var3=PI Var4=SP Var5=Plan
Var6=NP Var7=Sales Var8=RBS Var9=PG Var10=Fe Var12=Prescriber Var13= First_Name Var14=Middle_Initial Var15=Address Var16=City Var17=ST Var18=Zip Var19=NP Var20=Pay Var21=DD Var22=Data_Date Var23=Maximum_Number_of_Buckets
Var24=NRx_10_07_2016
Var25=NRx_09_30_2016
Var26=NRx_09_23_2016
Var27=NRx_09_16_2016
Var28=NRx_09_09_2016
Var29=NRx_09_02_2016
Var30=NRx_08_31_2016
Var31=NRx_08_26_2016
Var32=NRx_08_19_2016
Var33=NRx_08_12_2016
..
...
.....
..............
Var150=NRx_10_03_2014
Var151=NRx_09_30_2014
/* same values for TRx now */
Var152=TRx_10_07_2016
Var153=TRx_09_30_2016
Var154=TRx_09_23_2016
Var155=TRx_09_16_2016
........
..........
...........
Var278=TRx_10_03_2014
Var279=TRx_09_30_2014
/* same values for Nqty now */
Var280=Nqty_10_07_2016
Var281=Nqty_09_30_2016
.......
..........
..........
Var406=Nqty_10_03_2014
Var407=Nqty_09_30_2014
/* same values for Tqty now */
Var408=Tqty_10_07_2016
Var409=Tqty_09_30_2016
Var410=Tqty_09_23_2016
..........................
...............
.............
Var533=Tqty_10_10_2014
Var534=Tqty_10_03_2014
Var535=Tqty_09_30_2014 ) );
set master;
run;
now I want to reduce my efforts becoze when I receive file the date of var22 will be diff now and according to that date
I have maintain data for 24 months going backwards 7 days the same logic, that's why I need to rename my vars every time
when I get the file from client.
Please help me in here I hope I have made myself clear now.
Thanks
How are you dealing with these constantly changing variable names downstream? Doesn't that cause you quite a bit of headache everywhere.
What about storing the data in a long format and then if you really need it in a wide format eg. for timeseries use Proc Transpose.
Below a code sample storing the data in two tables. You can of course also create a single de-normalized table or further normalize your data. It's just an example to demonstrate the approach.
/** create sample data **/
%let source_file=c:\temp\test.csv;
data have;
array key_desc {21} $1 Client_Number Report_Number PI SP Plan NP1 Sales RBS PG Fe Var11 Prescriber First_Name Middle_Initial Address City ST Zip NP2 Pay DD (21*'C');
length report_date Maximum_Number_of_Buckets 8;
format report_date date9.;
report_date='7oct2016'd;
Maximum_Number_of_Buckets=3;
array NRX {127} $2 ('1' 126*'VA');
array TRX {127} $2 ('2' 126*'VB');
array Nqty {127} $2 ('3' 126*'VC');
array Tqty {127} $2 ('4' 126*'VE');
run;
proc export
data=have
outfile="&source_file"
replace
;
putnames=yes;
quit;
/** read sample data **/
data
cust_rep_dim (drop=bucket value date_point)
cust_rep_fact(keep=cust_rep_key report_date bucket value date_point)
;
length cust_rep_key $32;
length report_date Maximum_Number_of_Buckets 8;
format report_date date9.;
array key_desc {21} $1 Client_Number Report_Number PI SP Plan NP1 Sales RBS PG Fe Var11 Prescriber First_Name Middle_Initial Address City ST Zip NP2 Pay DD;
infile "&source_file" dlm=',' dsd truncover firstobs=2;
input
(Client_Number Report_Number PI SP Plan NP1 Sales RBS PG Fe Var11 Prescriber First_Name Middle_Initial Address City ST Zip NP2 Pay DD) (:$1.)
report_date :date9. Maximum_Number_of_Buckets :best32.
@;
cust_rep_key=put(md5(catx('|',of key_desc[*])),hex32.);
output cust_rep_dim;
length bucket $4 value $2;
format date_point date9.;
do bucket='NRX','TRX','Nqty','Tqty';
_cnt=0;
do _i=0 to 126 while(_cnt<=126);
_cnt+1;
input value :$2. @;
date_point=intnx('week',report_date,-_i,'s');
if intck('month',date_point,intnx('week',report_date,-_i+1,'s')) ne 0 and intnx('month',date_point,0,'e') ne date_point then
do;
date_point=intnx('month',report_date,-1,'e');
if not missing(value) then output cust_rep_fact;
if _cnt<126 then
do;
_cnt+1;
input value :$2. @;
date_point=intnx('week',report_date,-_i,'s');
if not missing(value) then output cust_rep_fact;
end;
end;
else
if not missing(value) then output cust_rep_fact;
end; /* end date loop */
end; /* end bucket loop */
drop _:;
run;
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!
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.