topic Errors in accounting for null values in SAS Procedures

Errors in accounting for null values

Q1983 — Tue, 01 Jul 2014 16:45:48 GMT

Here is the code

data test;

set test1;

if date ge anydate;

run;

proc format $cfmt '075'='fs075'

'079'='fs079'

'080'='fs080'

;

run;

proc transpose data= test out = test2 format step_cd $cfmt.

id step_cd;

by ln_no;

var anydate;

run;

proc sql;

create table test4 as

select a.ln_no,

b.fs075,

b.fs079,

b.fs080

from mytable a

left join test2 b

on a.ln_no=b.ln_no;

quit;

This works when the fs varaibles have data. When they are blank or null, I get errors. The vairable is not found or in correlation with b. How can I account for the variables in my code if they happen not to have values on a particular day? I this case the highlighted variables of fs075 079 or 080??

Re: Errors in accounting for null values

ballardw — Tue, 01 Jul 2014 17:38:39 GMT

if the value doesn't exist then it can't be transposed to create the variable.

One crude approach would be to create a data set with no values but the variables you need.

Then after the transpose:

data test2;

set emptyset test2;

run;

then the sql

Re: Errors in accounting for null values

Reeza — Tue, 01 Jul 2014 18:32:51 GMT

Your proc transpose code doesn't look correct, missing a semi colon and the format looks to be in the wrong place.

Depending on your data you may be best off adding it in prior to transposing using the preloadfmt option with proc tabulate/summary/means or after using some other method.

I could probably use some imagination and guess what your input table looks like, but its easier if you post sample data and expected output. A data step can also be used to efficiently transpose data.

Re: Errors in accounting for null values

Tom — Tue, 01 Jul 2014 20:25:58 GMT

Simplest way is to just use another data step to add the variables you want. Has the advantage of also allowing you to set the order, format, labels etc.

Note also that you can just use the PREFIX option on PROC TRANSPOSE to do what the FORMAT is doing for you.

data test;

input ln_no id step_cd $ anydate date9. ;

format anydate date9. ;

cards;

1 1 075 01JAN2013

1 2 079 01FEB2013

2 3 079 02FEB2013

2 4 075 01MAR2013

run;

proc print data=test; run;

%let possible_vars = fs075 fs079 fs080 ;

proc transpose data=test out=test1 prefix=FS ;

by ln_no;

id step_cd;

var anydate;

run;

proc print data=test1; run;

data test2;

retain ln_no ;

length &possible_vars 8;

format &possible_vars date9. ;

set test1 (drop=_name_);

run;

proc print data=test2; run;

Obs ln_no id step_cd anydate

1 1 1 075 01JAN2013

2 1 2 079 01FEB2013

3 2 3 079 02FEB2013

4 2 4 075 01MAR2013

Obs ln_no _NAME_ FS075 FS079

1 1 anydate 01JAN2013 01FEB2013

2 2 anydate 01MAR2013 02FEB2013

Obs ln_no fs075 fs079 fs080

1 1 01JAN2013 01FEB2013 .

2 2 01MAR2013 02FEB2013 .