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

I have a dataset with columns Month, A and Q as below:

 

MonthAQCalc
Jan-1910.00%  
Feb-1920.00%21.00%12.10%
Mar-1915.00%16.00%14.04%
Apr-1930.00%29.00%18.11%
May-1940.00%39.00%25.17%
Jun-1930.00%29.00%32.47%
Jul-1910.00%11.00%

36.04%

 

I want to create Calc, where first answer is 10%*(1+21%)=12.10%
The next row onwards uses the previous rows calc so 12.10%*(1+16%)=14.04%

 

What is the best way of doing this in SAS so it doesnt have to be done in Excel?

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

I am not sure what dataset you are running against and if you are able to follow the logic. Assuming your sample is a good representative sample, Here is my test using your sample.

 

data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;
proc print noobs;run;

RESULT:

Month A Q calc
JAN2019 10% . .
FEB2019 20% 21% 12.10%
MAR2019 15% 16% 14.04%
APR2019 30% 29% 18.11%
MAY2019 40% 39% 25.17%
JUN2019 30% 29% 32.47%
JUL2019 10% 11% 36.04%

 

.The calc variable is the new computed variable ( requirement)

 

View solution in original post

13 REPLIES 13
PaigeMiller
Diamond | Level 26

Are the values of A and Q on row two 0.20 and 0.21 respectively, or are they character strings with a % sign at the end?

--
Paige Miller
PetePatel
Quartz | Level 8

Hi Paige, they are numeric so 0.20 and 0.21

novinosrin
Tourmaline | Level 20
data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
/*10%*(1+21%)=12.10%*/
data want;
 set have;
 retain calc ;
 if _n_=1 then calc=a;
 else   calc=calc*(1+q);
 format calc percent8.2;
run;
novinosrin
Tourmaline | Level 20

More precise

 


data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;
PetePatel
Quartz | Level 8

Hi, this doesn't appear to run correctly. Calc is the newly created calculation so do you just need a and q in the dataset?

novinosrin
Tourmaline | Level 20

Hi @PetePatel  Here is my understanding

 

1. You have a dataset with 3 variables- Month, A and Q

2. You want to create a new variable by the name CALC using a logic/formula

3. I believe the the code that I wrote does that as I tested with the sample you gave me.

 

Am I missing something?

PetePatel
Quartz | Level 8

Hi @novinosrin ,

 

When I run the code through exactly as you've provided, it gives me a dataset with month  (Jan, Feb and May only) with A, Q and Calc as missing (.)

 

I also wondered why you specified the calc values upfront.

 

 

novinosrin
Tourmaline | Level 20

I am not sure what dataset you are running against and if you are able to follow the logic. Assuming your sample is a good representative sample, Here is my test using your sample.

 

data have;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;
proc print noobs;run;

RESULT:

Month A Q calc
JAN2019 10% . .
FEB2019 20% 21% 12.10%
MAR2019 15% 16% 14.04%
APR2019 30% 29% 18.11%
MAY2019 40% 39% 25.17%
JUN2019 30% 29% 32.47%
JUL2019 10% 11% 36.04%

 

.The calc variable is the new computed variable ( requirement)

 

PetePatel
Quartz | Level 8

Thanks.

 

That's incredibly strange as when I run your code through without making changes I get:


Month A Q calc
FEB2019...
MAY2019...

 

I wonder if it's a SAS issue

 

Tom
Super User Tom
Super User

@PetePatel wrote:

Thanks.

 

That's incredibly strange as when I run your code through without making changes I get:


Month A Q calc
FEB2019 . . .
MAY2019 . . .

 

I wonder if it's a SAS issue

 


Most likely a copy and paste issue.  Make sure that you have to inserted TAB characters into the text.

novinosrin
Tourmaline | Level 20

Hi @PetePatel  Riight, I can sense where the heck the problem  is . I believe you are executing in SAS EG am i right?

 

Like @Tom  pointed the TAB chars, add to the code and try

infile cards expandtabs;

Full version

 

data have;
infile cards expandtabs;
input Month	: monyy7. A	: percent2. Q  :percent2.;*	Calc;
format month monyy7. a q percent.;
cards;
Jan-19	10.00%	 	.   . 
Feb-19	20.00%	21.00%	12.10%
Mar-19	15.00%	16.00%	14.04%
Apr-19	30.00%	29.00%	18.11%
May-19	40.00%	39.00%	25.17%
Jun-19	30.00%	29.00%	32.47%
Jul-19	10.00%	11.00%	36.04%
;
data want;
 set have;
 retain  calc;
 if _n_=1 then do;
   output;
   calc=a;
 end;
 else do; 
   calc=calc*(1+q);
   output;
 end;
 format calc percent8.2;
run;
PetePatel
Quartz | Level 8

Yes sorry should have mentioned SAS EG.

 

Works perfectly now thanks!

Tom
Super User Tom
Super User

@PetePatel wrote:

Hi, this doesn't appear to run correctly. Calc is the newly created calculation so do you just need a and q in the dataset?


You do not want the new "retained" variable in the incoming dataset.  If it is there then when SAS reads the next observation the retained value is overwritten with the value read from the dataset.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 13 replies
  • 1694 views
  • 2 likes
  • 4 in conversation