topic Why the command statement didn't work? in SAS Programming

Why the command statement didn't work?

ybz12003 — Mon, 28 Nov 2022 19:26:02 GMT

Hello,

I would like to put the value either in "Pro" column or "Tec" column into a new column "Total_cost". The new column results are all missing, how come? All of the columns are numeric, please help. Thanks.

data want;
	set have;
	Format Total_Cost DOLLAR10.2;
        if class=9 then Total_cost=Pro;
	else if class in (1,2,3,4,5,6,7,8,10) then Total_Cost=Tec;
run;

Re: Why the command statement didn't work?

Tom — Mon, 28 Nov 2022 19:36:13 GMT

Look at your data to tell the answer.

What are the actual values of CLASS? If the value is not an integer from 1 to 10 then you never assign any value to TOTAL_COST.

What are the values of TEC and PRO? Do any of the observations have missing values? Any missing values that are selected by the IF/THEN/ELSE block will be propagated into TOTAL_COST.

Re: Why the command statement didn't work?

ybz12003 — Mon, 28 Nov 2022 20:29:06 GMT

Class is from 1 to 10.
Both TEC and PRO carry missing values. The sample values are shown below.
644.95
35130.1
8.02

Re: Why the command statement didn't work?

SASKiwi — Mon, 28 Nov 2022 20:46:36 GMT

Please post the log of your program. If TEC and PRO only contain missing values then your program isn't going to work as you wanted.

Re: Why the command statement didn't work?

Quentin — Mon, 28 Nov 2022 20:59:11 GMT

It's possible the values of class have unexpected values (maybe they are not actually integers). When you have an IF/ELSE IF block that you expect to be exhaustive, it's often helpful to add an ELSE statement that will write an error message to the log if any values do not fall into one of the expected bins, e.g.:

data want;
	set have;
	Format Total_Cost DOLLAR10.2;
	if class=9 then Total_cost=Pro;
	else if class in (1,2,3,4,5,6,7,8,10) then Total_Cost=Tec;
	else put "ERROR: unexpected value " class= ;
run;

If that code runs without errors, then I would check the values of PRO and TEC to make sure they are not null. You can also add an assertion to your code to check this:

data want;
	set have;
	Format Total_Cost DOLLAR10.2;
	if NOT (Pro >=0 ) then put "ERROR: " Pro= ;
	if NOT (Tec >=0 ) then put "ERROR: " Tec= ;
	if class=9 then Total_cost=Pro;
	else if class in (1,2,3,4,5,6,7,8,10) then Total_Cost=Tec;
	else put "ERROR: unexpected value " class= ;
run;

It also helps to check values by writing them to the log, with PUT statements, e.g.:

data want;
	set have;
	Format Total_Cost DOLLAR10.2;
	if NOT (Pro >=0 ) then put "ERROR: " Pro= ;
	if NOT (Tec >=0 ) then put "ERROR: " Tec= ;
	if class=9 then Total_cost=Pro;
	else if class in (1,2,3,4,5,6,7,8,10) then Total_Cost=Tec;
	else put "ERROR: unexpected value " class= ;
	put (Class Pro Tec Total_Cost)(=) ;
run;

If you run that last step, the log should make clear what is happening. If the log doesn't make sense to you, please post the log you get.

Re: Why the command statement didn't work?

ybz12003 — Tue, 29 Nov 2022 01:04:29 GMT

You are right; most of the values are not integers. I used your code to run my dataset, and tons of Error messages were shown in the log window. Below is some examples.

ERROR: Pro=.
ERROR: unexpected value class=9
class=9 Pro=. Tec=35130.1 Total_Cost=.

ERROR: Pro=.
class=5 Pro=. Tec=445.49 Total_Cost=445.49

Re: Why the command statement didn't work?

SASKiwi — Tue, 29 Nov 2022 02:29:44 GMT

Posting error messages without the code that is generating them isn't useful. Please post your COMPLETE SAS log including code, notes and errors.

Re: Why the command statement didn't work?

pink_poodle — Tue, 29 Nov 2022 02:55:22 GMT

Please try the following:
1. Comment out the format statement - does anything show up in total cost?
2. Run proc freq on class variable - are values there?

Re: Why the command statement didn't work?

ybz12003 — Tue, 29 Nov 2022 03:06:29 GMT

1. Nothing is shown in the Total cost.
2. Frequency counts are shown in the Proc freq class table.

Re: Why the command statement didn't work?

Tom — Tue, 29 Nov 2022 04:19:46 GMT

@ybz12003 wrote:
You are right; most of the values are not integers. I used your code to run my dataset, and tons of Error messages were shown in the log window. Below is some examples.

ERROR: Pro=.
ERROR: unexpected value class=9
class=9 Pro=. Tec=35130.1 Total_Cost=.

ERROR: Pro=.
class=5 Pro=. Tec=445.49 Total_Cost=445.49

IF you ran the code suggested (hard to tell since you did not show the full log) then the second ERROR line you show means that the value of CLASS that is printing as 9 is not actually 9.

Does it have any format permanently attached? What format?

If it is character you should have gotten messages in the log about converting characters to numbers. Like this:

1956  data test;
1957    class='9';
1958    if class in (9) then put 'equal 9';
1959  run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      1958:6
equal 9

In which case the only reason it would print like 9 but not equal 9 would be if it had some non-printing character, like a TAB or CARRIAGE RETURN character in it.

If it is numeric and has no format attached SAS will use the BEST12. format to display the value in that PUT statement. In which case it is very close to 9. You could try converting the CLASS to an integer using the INT() function. Or perhaps just round it case there might be decimal places you want to keep.