topic Re: Coding logic in SAS Programming

Coding logic

molla — Wed, 18 Nov 2020 17:56:20 GMT

Hi All,
I need to develop a logic, scenario given below:
I need to get the data of terminals after they have installed of first 6months
Example: If a terminal is installed in apr2020 they i need to get the data of Apr May Jun jully aug sep.similarly if terminal is installed in may i need to get data of May Jun jully aug sep oct.this data has to be picked automatically based on terminal installation month.

Kindly help me with the logic

Re: Coding logic

PeterClemmensen — Wed, 18 Nov 2020 18:00:10 GMT

Can you provide some sample of your data? Makes it easier to provide usable code

Re: Coding logic

Reeza — Wed, 18 Nov 2020 18:06:19 GMT

Depends on how your data is structured. If all your data for a terminal is in a single row then it's different than if you have multiple rows for each terminal.

INTNX() is a function you'll likely need. It will increment a date forward or backwards by a specified interval.

So you can find the end window via :

Install6Months = intnx('month', installDate, 6, 's') ;

Re: Coding logic

molla — Wed, 18 Nov 2020 18:13:25 GMT

By using intnx function i can get the 6 mnth but how can i get data of first month to sixth month by using this 2 months

Re: Coding logic

AMSAS — Wed, 18 Nov 2020 18:26:06 GMT

If date>=first_month and date<=sixth_month then output ;

Re: Coding logic

MarkDawson — Wed, 18 Nov 2020 18:33:13 GMT

It's always best if you can supply date both before and after the transformation you require, and details of what business logic gets you from one to the other. Assuming that you either ant 6 rows or 6 columns for each Terminal Installation, then this code should give you a solution that you can tune to meet your requirements

data sample ; 
   infile datalines truncover ; 
   input firstInstall date9. ;
   format firstInstall date9. ;
datalines ;
05May2020
31Jul2020
; run ;
data long_Results ;
   set sample ; 
   do i = 1 to 6 ;
      month = intnx('month',firstinstall,i-1) ;
      output ; 
   end ;
   format month date9. ;
   drop i ;
run ;
data wide_Results ;
   set sample ; 
   array months[6] ;
   do i = 1 to dim(months) ;
      months[i] = intnx('month',firstinstall,i-1) ;
   end ;
   format months1-months6 date9. ;
   drop i ;
run ;

Re: Coding logic

molla — Thu, 19 Nov 2020 04:17:10 GMT

Hi thanks for the code,based on that 6months i need to extract the data from seperate tables the table names contain month names,can we do that automatically in the same step,extracting the data month wise for the terminals

Re: Coding logic

molla — Thu, 19 Nov 2020 06:01:56 GMT

In other tables I have the volume of those installed terminals seperately,From Apr 2020 to Nov 2020 I have volumes ,but I need to get the volume for only six months for the months which we have populated,

Re: Coding logic

MarkDawson — Thu, 19 Nov 2020 08:47:37 GMT

I'm not sure what you mean - please could you give examples of the input table(s), and expected output table(s) results

Re: Coding logic

molla — Thu, 19 Nov 2020 09:00:57 GMT

I have populated the table based on your above code,
Installedtid mnth1 mnth2 mnth3 mnth4 mnth5 mnth6
Other tables I have installedtid and mnth1 vol similarly in other tables as well,
Now I need to get output table with installedtid mnth1volume mnth2volume mnth3volume mnth4volume mnth5vilume mnth6volume,
Even I have volumes of all months in the tables I need to get the volumes of 6mnths only which I have populated months

Re: Coding logic

Kurt_Bremser — Thu, 19 Nov 2020 09:13:48 GMT

I fear we still do not have a good clue about your data structure and contents.

Please supply data. Don't talk about it, don't try to describe it, show it.

Use a data step with datalines (or any other means, like loops) that creates a dataset we can use for developing and testing, like Novinosrin showed you here.

Then show the expected output from that particular dataset.

Re: Coding logic

molla — Thu, 19 Nov 2020 09:27:23 GMT

Table1
Id mnth1 mnth2 mnth3 mnth4 mnth5 mnth6
1. Apr20 may20 jun20 jul20 aug20 sep20

Table2
Id mnth vol
1 apr20 12
Table3
Id mnth vol
1 may20 15
Similar for other mnths also I have data

Output dataset
Id Apr May Jun jul aug sep
1. 12 15 ....
If terminal is installed in may then i should get the vol from may Oct

Re: Coding logic

Kurt_Bremser — Thu, 19 Nov 2020 09:48:35 GMT

If your data really looks like that ("dates" as strings, wide layout), you suffer from useless data in a useless structure.

See this short example for intelligent data making the code simple:

data table1;
input id monthcount mnth :yymmn6.;
format date yymmd7.;
datalines;
1 1 202004
1 2 202005
1 3 202006
1 4 202007
1 5 202008
1 6 202009
;

data m1;
input id mnth :yymmn6. volume;
format mnth yymmd7.;
datalines;
1 202004 12
;

data m3;
input id mnth :yymmn6. volume;
format mnth yymmd7.;
datalines;
1 202005 15
;

data all / view=all;
set m:;
run;

data want;
merge
  table1
  all
;
by id mnth;
run;

proc report data=want;
column id mnth,volume;
define id / group;
define mnth / "Volume" across;
define volume / "" analysis sum;
run;

To show you how to make intelligent data out of what you have, supply it in DATA STEPS WITH DATALINES, as I'm not into the guessing game.