DATA Step, Macro, Functions and more

sum variables to create new variables and missing

Reply
Occasional Contributor
Posts: 5

sum variables to create new variables and missing

Hi, 

I am trying to create a new variable called "Diff" by subtracting two columns. When those columns have missing variables, I want diff to be missing too. In a data step, I tried "diff = sales-cost;" and got an error. Then I tried "diff=sum(sales,-cost);" which worked but gave me values for all of diff, when there should be some missing. I want it to look like:

 

sales     cost     diff

.            8.99       . 

 

and right now it looks like:

sales     cost      diff

.            8.99    -8.99

 

Thanks!

Contributor
Posts: 44

Re: sum variables to create new variables and missing

What's the error you are getting. I ran the sample code and I get the output as you wanted.

Also refer the difference between sum statement and sum variable.

 

http://stackoverflow.com/questions/30677244/difference-between-sum-statement-and-sum-variable-in-sas

data have ;
input sale cost ;
Diff=sale-cost ;
datalines ;
. 10.23
15 3.72
17.23 .
;
run ;

proc print data=have ;run ;
Occasional Contributor
Posts: 5

Re: sum variables to create new variables and missing

Thank you! Now I am using "diff=price-sales;" and it works, however I still need to have missing values in diff if price or sales is missing

Contributor
Posts: 44

Re: sum variables to create new variables and missing

You will continue to get it if you use price-sales. Are you facing any issues now ? I mean not getting missing values when prices/sales is missing ?
Occasional Contributor
Posts: 5

Re: sum variables to create new variables and missing

Yes--right now I am not getting any missing values in diff. I want missing values

Valued Guide
Posts: 797

Re: sum variables to create new variables and missing

please show a case where you use

   diff=sales-priice

and you fail to get diff=. when either sales or price (or both) is missing.

 

... unless you have a subsequent "diff=' assignment later in the program.

Occasional Contributor
Posts: 5

Re: sum variables to create new variables and missing

I do reference diff again, as I am making another categorical variable called "change"

 

 

data prices;

     input unitcostprice unitsalesprice;

     diff = unitsalesprice - unitcostprice;
     if diff <= 10 then Change="LOW";
     else if 10 < diff <= 20 then Change="MED";
     else Change="HI";
run;

There are no missing values for change and I want there to be.

 

Thanks!

Valued Guide
Posts: 797

Re: sum variables to create new variables and missing

Diff is missing,  but missing values compare low to all valid numerics (like 10). So change='LOW'.

 

Use

   if not(missing(diff)) and diff < 10 then change='LOW'; 

Contributor
Posts: 44

Re: sum variables to create new variables and missing

Use the below code. I added one more condition. Since your first condition checks for any values less or equal to 10 the nulls/missing falls into that bucket. You can correct it by checking for them.

 

data have ;
input sale cost ;
Diff=sale-cost ;
 if diff <= 10 and diff > 0 then Change="LOW";
     else if 10 < diff <= 20 then Change="MED";
     else Change="HI";
datalines ;
. 10.23
15 3.72
17.23 .
;
run ;

proc print data=have ;run ;
Super User
Posts: 5,083

Re: sum variables to create new variables and missing

You do need to change the logic, but don't do it this way.  All the missing values for DIFF will be classified as "HI".

Ask a Question
Discussion stats
  • 9 replies
  • 371 views
  • 0 likes
  • 4 in conversation