BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LittlesasMaster
Obsidian | Level 7

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
Opal | Level 21

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;
 
  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 16 replies
  • 2814 views
  • 2 likes
  • 5 in conversation