topic Re: Generate strings using macro in SAS Programming

Generate strings using macro

LittlesasMaster — Sun, 13 Nov 2016 11:39:33 GMT

Hi,

I am stuck into some real time situation, every month I get a notepad file without headers and I do use proc import to get the data in SAS dataset. Now in this notepad there are so many fields(aprox 500) so proc import give the variable names as Var1 Var2 ...Var500

So once I have created the dataset , now I rename the variables starting from var24.

so the pattern is somehting like in one varibale lets say in var22 I get a date, lets say it start date then I need to trac data for 24 months back.

Example- lets say in var22 I have date - 13-Oct-2016

now I will rename the vars starting from var24 like this.

Rename(

var24=V_10_13_2016

Var25=V_10_06_2016

Var26=V_09_30_2016

Var27=V_09_23_2016

Var28=V_09_16_2016

Var27=V_09_09_2016

...................................

till 24 months.

Now if you see the pattern we go back 7 days but if that changes the month then we stop at the last day of the month, see above

Var25=V_10_06_2016

Var26=V_09_30_2016

Now If I can write a macro to create such string Var27=V_09_09_2016 like this then my task will be easy and I can redduce my work hrs.

Please help me in here. Thanks

Re: Generate strings using macro

Shmuel — Sun, 13 Nov 2016 20:23:35 GMT

Adapt next macro/code to your needs:

%macro ren_vars(v2start=24, 
                v2end=40,    
                from_date=13-Oct-2016);
    data _null_;
       length ren_all $10000 renx $25 datex $10;
       datex = compress("&from_date",'-')||'d';
       from_date = input(datex,date9.); *put from_date=;
       vnum = &v2start;
       varx = "var&v2start";
       
       do until (vnum = &v2end);
          datex = put(from_date,mmddyy10.);
          renx  = cat(varx,' = ',"v_",datex); *put renx=;
          ren_all = trim(ren_all) ||' '|| renx;
          if day(from_date) > 7 then from_date = from_date - 7;
          else from_date = intnx('month',from_date,-1,'e');
          vnum+1;
       end;
       call symput('ren_all',trim(ren_all));
   run;
   %put ren_all=&ren_all;   /* check results in the log */
%mend ren_vars;
%ren_vars(v2start=24, 
          v2end=40,     /* addapt to needs, 500 ? */
          from_date=13-Oct-2016);

Re: Generate strings using macro

Patrick — Sun, 13 Nov 2016 21:48:21 GMT

I feel that instead of creating logic to rename the variables, you should be trying to come up/ask for logic to transpose your data from a wide into a long structure. That could make things quite a bit easier.

It would also help a lot if you could post some representative sample data.

Re: Generate strings using macro

ballardw — Mon, 14 Nov 2016 11:51:34 GMT

If the file is supposed to be in the same format then you should only use proc import one time. Proc import when reading text files will create data step code to read the file. Look in the log after importing one of these files to see the code. Copy it from the Log to an editor and make needed changes such as specifying the variable names, double checking on informats to maintain consistent types and lengths of character variables and possibly adding labels. Then you only need to change the name of the input file and output data set.

If you continually use Proc Import you will with very high probability end up with variables of different types, numeric and text, or different length character variables.

If the file format changes then it still may be easier to create incremented code to read the desired variables it just adds a step for you to tell the program the start end dates.

Note that if the data was actually in long form:

Date Value instead of having a separate column for each value the data would be much easier to handle in the long run.

Re: Generate strings using macro

LittlesasMaster — Tue, 15 Nov 2016 17:11:40 GMT

So here is what i do everyday.

Firrt run Proc Import and get the text file in SAS.

Proc import datafile="File.txt" out=mydataset

dbms=dlm replace;

delimiter=";";

getnames=no;

run;

Data want;

set mydataset(rename=

(

ar24=V_10_13_2016

Var25=V_10_06_2016

Var26=V_09_30_2016

Var27=V_09_23_2016

Var28=V_09_16_2016

Var27=V_09_09_2016

/*for all 500 vars */)

run;

now want data is ready for me to work.

So I was trying to create a macro so that I can replace that I can call my macro

rename=(%mymacro);

Please help me in here.

Thanks!

Re: Generate strings using macro

Shmuel — Tue, 15 Nov 2016 20:09:27 GMT

I posted you such macro name REN_VARS geting 3 arguments:

(1) v2start = 24 as you wanted to rename from var24 on

(2) v2end = 40 as you said that your last var to rename is var40

(3) from_date = 13-OCT-2016 as you computed dates startin with OCT 13th, 2016

going backwards (to the past) decresing 7 days except when mont changes then last day in month.

The macro REN_VARS creates a string like:

var24=V_10_13_2016 var25=V_10_06_2016 var26=V_09_30_2016 ... etc

The result will be in a macro variable named ren_all to be used as:

data want;

set mydataset (rename=( &ren_all));

...

run;

Re: Generate strings using macro

LittlesasMaster — Tue, 22 Nov 2016 18:10:52 GMT

Thanks Shmuel, this is what I was looking towards, I am working on your code to make it work for me for all

rest of the vars.

your help highly appreciated.

Re: Generate strings using macro

LittlesasMaster — Sun, 27 Nov 2016 16:17:12 GMT

Hi Shmuel,

I am trying to run your code and the result I am getting is like this

var24 = v_10/13/2016 var24 = v_10/06/2016 var24 = v_09/30/2016 var24 = v_09/23/2016......

but if you see var24 is costant and date should be in underscore separated not by '/';

so desired output should be like this

var24 = v_10_13_2016 var25 = v_10_06_2016 var26 = v_09_30_2016 var27 = v_09_23_2016....

Please help me in here.

Thanks

Re: Generate strings using macro

Tom — Sun, 27 Nov 2016 16:43:18 GMT

It would seem to me that you should just skip the PROC IMPORT and just read your data file directly.

Can you explain the data structure in more detail?

So column 22 has the date? And column 24 onward are values for follow-up dates that are based on that starting date? Does that extend to the end of the line?

What do the first 21 columns contain?

What does column 23 contain?

What happens on the next observation? Is column 22 the same for every row in the source file? If not then your RENAME method is bound to fail.

What structure would you like for the data? I would assume that it would make much more sense to have a series of observations with DATE/VALUE pairs instead of putting the date information into the NAME of the variable.

How are you going to use the data?

Re: Generate strings using macro

Tom — Sun, 27 Nov 2016 17:12:41 GMT

This is going to be much easier to do with a DATA step than macro code.

Let's make a simple example dataset that has VAR22 and some other variables.

data have ;
  var22 = '13oct2016'd ;
  length var24 - var200 8 ;
run;

Now let's write a DATA _NULL_ step to write out a RENAME statement into a text file. I have adjusted it to use YYYY-MM-DD format instead of MM-DD-YYYY format because then the variable names will sort alphabetically into the proper order.

filename code temp;
data _null_;
  file code ;
  set have (obs=1 keep= var22);
  put 'rename ' ;
  previous = var22;
  date     = var22;
  format var22 previous date YYMMDD10. ;
  length varname newname $32 ;
  do i=24 by 1  ;
    varname=cats('var',i);
    date = intnx('day',previous,-7);
    if month(date) ^= month(previous) then date=intnx('month',date,0,'end');
    if (date <= intnx('month',var22,-24)) then leave ;
    newname = 'V_' || translate( put(date, yymmdd10.) , '_','-') ;
    put varname '=' newname ;
    previous=date ;
  end;
  put ';' ;
run;

Then you can just %INCLUDE that statement where you need it.

data want ;
  set have ;
  %inc code / source2 ;
run;

Results

106  data want ;
107    set have ;
108    %inc code / source2 ;
NOTE: %INCLUDE (level 1) file CODE is file
      /.../#LN00013.
109 +rename
110 +var24 =V_2016_10_06
111 +var25 =V_2016_09_30
112 +var26 =V_2016_09_23
113 +var27 =V_2016_09_16
114 +var28 =V_2016_09_09
115 +var29 =V_2016_09_02
116 +var30 =V_2016_08_31
117 +var31 =V_2016_08_24
118 +var32 =V_2016_08_17
119 +var33 =V_2016_08_10
120 +var34 =V_2016_08_03
121 +var35 =V_2016_07_31
122 +var36 =V_2016_07_24

Re: Generate strings using macro

LittlesasMaster — Sun, 27 Nov 2016 17:26:42 GMT

Hi Tom,

I really apriciate your intrest into this so here are the answers of your questions

Does that extend to the end of the line? yes from 24 to 535 vars

set of three group of vars var24 to var151 and var152 to v279 and (next two set of 127 vars in each ).....till 535

What do the first 21 columns contain? these are Client_Number,Report_Number,Prescriber_ID.. etc.... and these are fixed so I can easly rename this and they never change in any file we receive from client.

What does column 23 contain? Maximum_Number_of_Buckets

so data structure is something like

var1 to var23 constant var names and from var24 to var 535 each of 127 set of vars

will have date values like I said going back 7 days from start date(var22) and if next month then end of month.

hope this will help

thanks!

Re: Generate strings using macro

Tom — Sun, 27 Nov 2016 17:53:02 GMT

Sounds like you can just write a single data step to read the file.

The program could look something like this. Fill in the names and types for the rest of the first 23 variables and attach any informats or formats that they need (don't attach formats that they don't need). Make some more meaningful variable names for your three arrays of readings.

data want ;
  infile 'myfile' dsd dlm=';' truncover ;
  length Client_Number $10
             Report_Number $12 
             ...
             Report_Date 8
             Maximum_Number_of_Buckets 8
  ;
  informat Report_Date date9. ;
  format Report_Date Date date9. ;
  array A (127);
  array B (127);
  array C (127);
  input Client_Number -- Maximum_Number_of_Buckets a1-a127 b1-b127 c1-c127;
  previous = Report_Date ;
  do i=1 to Maximum_Number_of_Buckets ;
     date = intnx('day',previous,-7) ;
     if month(date) ne month(previous) then date=intnx('month',date,0,'end');
     reading1 = a(i);
     reading2 = b(i);
     reading3 = c(i);
     output;
     previous = date;
  end;
  drop a1-a127 b1-b127 c1-c127 previous i ;
run;

This is assuming that the values are aligned into the 127 cells. So if there are 3 buckets you have 3 values then 124 empty values then 3 values etc. If instead the empty cells are all at the end so that you have A,A,A,B,B,B,C,C,C,.,.,. then just use one array and calculate where to find the right values.

     reading1 = a(i);
     reading2 = a(Maximum_Number_of_Buckets + i);
     reading3 = a(2*Maximum_Number_of_Buckets + i);

Re: Generate strings using macro

Shmuel — Sun, 27 Nov 2016 18:08:45 GMT

Sorry, I was too buisy last days.

The rename you got:

var24 = v_10/13/2016

is the result of line (in the macro)

datex = put(from_date,mmddyy10.);

Just add next line after it, inside the macro:

datex = translate(datex , '_' , '/');

to get the desired format:

var24 = v_10_13_2016

Re: Generate strings using macro

LittlesasMaster — Sun, 27 Nov 2016 18:16:34 GMT

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

Re: Generate strings using macro

Patrick — Sun, 27 Nov 2016 22:46:41 GMT

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;

Re: Generate strings using macro

LittlesasMaster — Tue, 03 Jan 2017 17:58:28 GMT

Hey Shmuel,

I had created the macro and it's working fine but there is a slite change is output I require, I tried so many things but this is not working so I need ur help in here..

I have written this code-

/* is how I get date part, its okay I can deal with this*/

data _null_;
date1="071016";
dd=substr(date1,1,2);
mm=substr(date1,3,2);
yy=substr(date1,5,2);
datex=mdy(mm,dd,yy);
call symputx("from_date",datex,'G');
run;

/* now I have got the date I will above date to start creating my values */

%macro ren_vars;
data _null_;
length NRx $10000 renx $25 datex $10;
datex = compress("&from_date");
from_date = input(datex,10.);
format from_date mmddyy10.;
do vnum=24 to 151;
datex = put(from_date,mmddyy10.);
renx = cat("Var",vnum,' = ',"NRx_",datex);
renx=tranwrd(renx,"/","_");
NRx = trim(NRx) ||' '|| renx;
if day(from_date) > 7 then from_date = from_date - 7;
else from_date = intnx('month',from_date,-1,'e');
end;
call symputx('NRx',trim(NRx),'G');
run;
%put ------------------Nrx-------------------------------------;
%put &NRx;

%mend ren_vars;

%ren_vars;

look at the output I am getting--- few lines of output shown below

Var24 = NRx_10_07_20-16 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_24_2016
Var32 = NRx_08_17_2016 Var33 = NRx_08_10_2016 Var34 = NRx_08_03_2016 Var35 = NRx_07_31_2016
Var36 = NRx_07_24_2016 Var37 = NRx_07_17_2016 Var38 = NRx_07_10_2016 Var39 = NRx_07_03_2016
Var40 = NRx_06_30_2016 Var41 = NRx_06_23_2016 Var42 = NRx_06_16_2016 Var43 = NRx_06_09_2016

what I want this is when it reaches to end of the month logic like we are checking if day>7 then keep substratcing 7 days from from_date but if day is <7 then that means it's going to next month so what we are asking system to go end of prev month and

start over doing that -7 day logic but i want here that our code should split the 7 days logic if u look at the above code in line 2

Var29 = NRx_09_02_2016 Var30 = NRx_08_31_2016 Var31 = NRx_08_24_2016

when our code came at 2nd day it found that ohh ok now this is less than 7 means go to end of pev month till this we are fine

Var30 = NRx_08_31_2016 but now Var31 should only substract 5 days and should give result as Var31=NRx_08_26_2016

so if you look colsely from 09-02 to 08-31 two days and then from 08-31 to 08-26 five days total comes 7 days

this is what I said split week, I am not able to find out logic , pls guide.

below are the sample output to make u better understand the output which I am looking for---

----want----

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
Var34=NRx_08_05_2016 Var35=NRx_07_31_2016 Var36=NRx_07_29_2016 Var37=NRx_07_22_2016 Var38=NRx_07_15_2016
Var39=NRx_07_08_2016 Var40=NRx_07_01_2016 Var41=NRx_06_30_2016 Var42=NRx_06_24_2016 Var43=NRx_06_17_2016
Var44=NRx_06_10_2016 Var45=NRx_06_03_2016 Var46=NRx_05_31_2016 Var47=NRx_05_27_2016 Var48=NRx_05_20_2016

Re: Generate strings using macro

Shmuel — Tue, 03 Jan 2017 21:56:05 GMT

1) Pay attention, MDY function creates a numeric sas date variable containing

the number of days past since January 1st, 1960 up to given date.

You can shorten 1st step to:

data _null_;
datex = input("07102016",ddmmyy10.) ;

/* or datex = '07OCT2016'd; */
call symputx("from_date",strip(datex),'G');
run;

2) As from_date is a number you can make your code more simple:

instead:

%macro ren_vars;
data _null_;
length NRx $10000 renx $25 datex $10;
datex = compress("&from_date");
from_date = input(datex,10.);

you better use:

    %macro ren_vars;
      data _null_;
      length NRx $10000 renx $25 datex $10;
      from_date = &from_date;

3) As to splitting 7 days when changing month:

instead

do vnum=24 to 151;
     datex = put(from_date,mmddyy10.);
     renx = cat("Var",vnum,' = ',"NRx_",datex); 
     renx=tranwrd(renx,"/","_");
     NRx = trim(NRx) ||' '|| renx;
     if day(from_date) > 7 then from_date = from_date - 7;
     else from_date = intnx('month',from_date,-1,'e');
end;

you need:

do vnum=24 to 151;
     datex = put(from_date,mmddyy10.);
     renx = cat("Var",vnum,' = ',"NRx_",datex); 
     renx=tranwrd(renx,"/","_");
     NRx = trim(NRx) ||' '|| renx;
     if day(from_date) > 7 then from_date = from_date - 7;
     else do;
             diff = day(from_date);
             from_date = intnx('month',from_date,-1,'e');
             datex = put(from_date,mmddyy10.);
             renx = cat("Var",vnum,' = ',"NRx_",datex); 
             renx=tranwrd(renx,"/","_");
             NRx = trim(NRx) ||' '|| renx;    

            if vnum = 151 then leave;
            vnum = vnum +1;
            from_date = from_date - 7 + diff;         
     end;
end;
DROP diff;