Generate strings using macro

[LittlesasMaster]

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

[Patrick]

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;