BookmarkSubscribeRSS Feed
SMohanReddy
Obsidian | Level 7

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.

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
SMohanReddy
Obsidian | Level 7

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

SMohanReddy
Obsidian | Level 7

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SMohanReddy
Obsidian | Level 7

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.

Ksharp
Super User

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;

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
  • 7 replies
  • 1467 views
  • 0 likes
  • 3 in conversation