BookmarkSubscribeRSS Feed
Lnicholl
Calcite | Level 5

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!

9 REPLIES 9
anoopmohandas7
Quartz | Level 8

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 ;
Lnicholl
Calcite | Level 5

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

anoopmohandas7
Quartz | Level 8
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 ?
Lnicholl
Calcite | Level 5

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Lnicholl
Calcite | Level 5

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!

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
anoopmohandas7
Quartz | Level 8

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 ;
Astounding
PROC Star

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

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
  • 9 replies
  • 6525 views
  • 0 likes
  • 4 in conversation