topic Re: sum variables to create new variables and missing in SAS Programming

sum variables to create new variables and missing

Lnicholl — Tue, 07 Feb 2017 17:05:58 GMT

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!

Re: sum variables to create new variables and missing

anoopmohandas7 — Tue, 07 Feb 2017 17:10:42 GMT

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 ;

Re: sum variables to create new variables and missing

Lnicholl — Tue, 07 Feb 2017 17:14:50 GMT

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

Re: sum variables to create new variables and missing

anoopmohandas7 — Tue, 07 Feb 2017 17:18:38 GMT

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 ?

Re: sum variables to create new variables and missing

Lnicholl — Tue, 07 Feb 2017 17:20:27 GMT

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

Re: sum variables to create new variables and missing

mkeintz — Tue, 07 Feb 2017 17:23:17 GMT

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.

Re: sum variables to create new variables and missing

Lnicholl — Tue, 07 Feb 2017 17:27:11 GMT

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!

Re: sum variables to create new variables and missing

mkeintz — Tue, 07 Feb 2017 17:30:50 GMT

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';

Re: sum variables to create new variables and missing

anoopmohandas7 — Tue, 07 Feb 2017 17:31:07 GMT

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 ;

Re: sum variables to create new variables and missing

Astounding — Tue, 07 Feb 2017 18:53:22 GMT

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".