BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

4 REPLIES 4
Reeza
Super User

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


 

ballardw
Super User

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.

Astounding
PROC Star

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.

mpordon
Fluorite | Level 6

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;

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