DATA Step, Macro, Functions and more

Looping and array Help

Reply
Occasional Contributor
Posts: 19

Looping and array Help

I have attached the sample data for the query I am looking for. I am deriving a column 'Total_Due_Last_4_Months' based on column A_20151201. If the year is 2015 and month is 11 in the column 'A_20151201' then i need to sum last 3 months along with 11 month as well. It will like sum(total_due_201511,total_due_201510,total_due_201509,total_due_201509). If the month is 10 then it is sum(total_due_201510,total_due_201509,total_due_201508,total_due_201507). I want it in a loop.

Super User
Super User
Posts: 7,942

Re: Looping and array Help

Posted in reply to SMohanReddy

Hi,

 

Why do you have the data in the format?  You would be better off, normalising that data, so have many rows for each id, each one for a separate date.  Is this some sort of report you have read in?  As I am not typing in that spreadsheet, I can't really provide any code (need test data in the form of a datastep), but generally:

proc transpose data=have out=want;
  by id;
  var _all_;
run;

This should give you something like:

id    var                       res

1     total_due_201501  1

1     total_due_201502  2

...

 

The next step is to separate out the data from var and create a proper date variable. e.g:

 

data want (drop=var);
  set want;
  thedate=input(compress(var," ","kd")||"01",yymmdd10.);  /* You will need to change for your cols */
run;

Then you have a dataset you can acutally work with.  Whilst you could do:

data have;
  set have;
  array a{3} total_due_201501 ...;
...
run;

Type looping, you don't really know that a{2} reflects 201503, I mean if the columns are not in order, or one is missing etc.  Its not robust programming.  This is why data should never appear as columns headings other than when creating output reports for review, data should always be in the data area to allow programming to operate on it.

Super User
Super User
Posts: 7,942

Re: Looping and array Help

A quick example:

data have;
  id=1; total_due_201501=1; total_due_201502=2; total_due_201503=3; 
  total_due_201504=4; total_due_201505=5; total_due_201506=6; total_due_201507=7; a_20150112='02012015'; output;
run;

proc transpose data=have out=want; 
  by id a_20150112;
  var _numeric_;
run;

data want (keep=id thedate comp_date col1);
  set want (where=(_name_ ne "id"));
  thedate=input(compress(_name_," ","kd")||"01",yymmdd10.);
  comp_date=input(a_20150112,ddmmyy10.);
  format thedate comp_date date9.;
run;

proc sql;
  create table RESULT as
  select  distinct A.ID,
          (select sum(COL1) from WANT where ID=A.ID and month(A.COMP_DATE) <= month(THEDATE) <= (month(A.COMP_DATE)+2)) as RESULT
  from    WANT A;
quit;
Occasional Contributor
Posts: 19

Re: Looping and array Help

Hi Thank you for the support. But I am not getting the output desired. I need to load the data as is how it its in the INPUT tab..the output is in the output tab. But in the output tab..i am deriving a column total_due_last_4_months based on a_20151201. This column has got values. Based on the value that is available i need to calculate last 4monhts total due by summing it up. the layout of output how i am looking is in output tab

Occasional Contributor
Posts: 19

Re: Looping and array Help

Posted in reply to SMohanReddy

Hi. .I need to load the data as is how it its in the INPUT tab..the output is in the output tab. But in the output tab..i am deriving a column total_due_last_4_months based on a_20151201. This column has got values. Based on the value that is available i need to calculate last 4monhts total due by summing it up. the layout of output how i am looking is in output tab. 

Super User
Super User
Posts: 7,942

Re: Looping and array Help

Posted in reply to SMohanReddy

Sorry, you will need to be clearer, starting with what software you are using.  Is it enterprise guide or university edition or some other package.  I do not know what "load the data" means, nor do I know what an "input" tab is.  You mention an "output", is this data we are talking about a report, if so then go back further than that and look at the process/code which generates that output.  Remember the "data" you use to program with, doesn't necessarily need to look like the final report output, they are used for different things.

Occasional Contributor
Posts: 19

Re: Looping and array Help

sorry for the confusion from my side. I am using Base SAS 9.4., I just shared a sample of the dataset in an excel file which has got two tabs. 'Input' and 'Output' tab. I need to use the data which is in INPUT tab to just derive a variable 'Total_Due_Last_4_Mnths' based on the values that are available in A_20151201. If the value is  11/1/2015, I need to sum the last 3monts along with Nov month. Here is an example. Sum( Total_Due_201511, Total_Due_201510, Total_Due_201509, Total_Due_201508). I have given the same in OUTPUT tab for reference by color coding. I just need to derive the variable. The output should be same as is how its in the output tab. I have attached the file again.

Super User
Posts: 10,023

Re: Looping and array Help

Posted in reply to SMohanReddy

proc import datafile='/folders/myfolders/Sample_Data.xlsx' out=have dbms=xlsx replace;
run;


data want;
 set have;
 array x{*} total_due_:;
 Total_Due_Last_4_Months=0;
 do i=1 to dim(x);
  date=input(scan(vname(x{i}),-1,'_'),yymmn6.);
  if intnx('month',A_20150112,-3) le date le intnx('month',A_20150112,0)
    then Total_Due_Last_4_Months+x{i};
 end;
 drop i date;
 run;
Ask a Question
Discussion stats
  • 7 replies
  • 430 views
  • 0 likes
  • 3 in conversation