topic Re: Macro to assign a values when variable is missing in SAS Programming

Macro to assign a values when variable is missing

Q1983 — Wed, 15 Nov 2017 21:35:21 GMT

data pop1;

set pop;

run;

OUTPUT
STATE      LOANS   AMT
AL                  1         100
AZ                  2          10
CA                3            50

Now lets say that no data was found for AZ. The results would be
STATE      LOANS   AMT
AL                  1         100
CA                3            50

Is there a Macro I can insert that says that if there were 0 loans for a particular state you show the state and a 0 for both loans and amt
STATE      LOANS   AMT
AL                  1         100
AZ                  0          0
CA                3            50

Re: Macro to assign a values when variable is missing

Reeza — Wed, 15 Nov 2017 21:56:47 GMT

Not a macro, but search PRELOADFMT and/or CLASSDATA

Basically, if the data doesn't exist at all, how do you tell SAS it exists? You need to do so in some manner, and PRELOADFMT and CLASSDATA are two options to do that.

If the data does exist in some manner, just not that summary table, there may be other ways, but given what you've posted, I strongly suspect that you're looking for the answer above.

@Q1983 wrote:

data pop1;

set pop;

run;

OUTPUT
STATE      LOANS   AMT
AL                  1         100
AZ                  2          10
CA                3            50

Now lets say that no data was found for AZ. The results would be
STATE      LOANS   AMT
AL                  1         100
CA                3            50

Is there a Macro I can insert that says that if there were 0 loans for a particular state you show the state and a 0 for both loans and amt
STATE      LOANS   AMT
AL                  1         100
AZ                  0          0
CA                3            50

Re: Macro to assign a values when variable is missing

ballardw — Wed, 15 Nov 2017 22:11:00 GMT

Preloadfmt only works with a few procedures: Procs Report, Tabulate, Means and Summary but here's a brief example:

data junk;
   input state $ loans amt;
datalines;
AL 1 100
CA 3  50
;
run;

proc format library=work;
value $mystates (notsorted)
'AL'= 'AL'
'AZ'= 'AZ'
'CA'= 'CA'
;
run;

proc tabulate data=junk;
   class state /  preloadfmt order=data;
   format state $mystates. ;
   var loans amt;
   table state='',
         (loans amt) * sum=""*f=best8.
         /misstext='0' printmiss  box=state
   ;
run;

If you need this in a dataset you may need to use Proc Means/summary but the statistics will likely be missing and you will need to take a pass through the output from the procedure to assign the missing values to 0.

Another approach is to have a data set of the values and Merge (data step) or Join (proc sql) the set with all the state values. But you'll still need to add the 0 values with some code.

Re: Macro to assign a values when variable is missing

Astounding — Thu, 16 Nov 2017 00:08:43 GMT

Your program shows a data set, not a report. Is that what you want? If so, you need to have a data set with just one variable (STATE) that includes every possible value for STATE. Otherwise SAS has no idea what the list of states should be.

If you are looking for a report instead of a data set, that's a different story.

Re: Macro to assign a values when variable is missing

mpordon — Wed, 22 Nov 2017 16:57:40 GMT

If you are trying to accomplish this in a data step you can simply use a base table that has a record for every state you may have information for. Then simply merge your pop dataset to the base dataset and set null values to 0.

*Your base table of states;
proc sql noprint;
	Create Table StateAbbrevs As
		Select distinct
			STATECODE As State
		From
			SASHelp.ZipCode;
quit;


data want;
	merge StateAbbrevs pop;
	by State;
	Loans = coalesce(Loans,0);
	Amt = coalesce(Amt,0);
run;