Retaining proc format values in the print regardless of the raw values

[Data_User]

Hi All,

Is there a way to retail the proc format values in the proc print regardless of the raw values? 

Below is the below sample data & the code. I need to have the proc format values in the output even there is no data for the proc format buckets. when there is no data it is fine to populate it with 0 values. Please let me know if there is a way to do this. Thanks

Proc format buckets:
"0"
"1-1,000"
"1,001-10,000"
"10,001-20,000"
"20,001-30,000"
"30,001-40,000"
"40,001-50,000"
"50,001-60,000"
"GT 60,000"
"ND"

Sample data & the Code:
data test1;
infile datalines dsd missover dlm="#";
input VAR1$ VAR2$ VAR3$ ID;
datalines;
LLLL#1#ND#101
MMMM#2#2454#101
XXXX#3#0#102
YYYY#4#195#101
PPPP#6#70000#102
ZZZZ#7#25000#104
;
run;

Proc format;
value gfmt
low-0 = " 0"
1-1000 = " 1-1,000"
1001-10000 = " 1,001-10,000"
10001-20000 = " 10,001-20,000"
20001-30000 = " 20,001-30,000"
30001-40000 = " 30,001-40,000"
40001-50000 = " 40,001-50,000"
50001-60000 = " 50,001-60,000"
60001-high = " GT 60,000"
other = "ND"
;
run;

　
data test2;
set test1;
if VAR3 = "ND" then VAR4=. ;
else VAR4=input(VAR3,12.);
run;
　
%macro M_Format (app);
proc sql;
create table test3 as
select strip(&app.) as Group
,case when VAR1 = "LLLL" and ID in (101) then "USA"
when VAR1 = "MMMM" and ID in (101) then "ASIA"
when VAR1 = "XXXX" and ID in (102) then "AUSTRALIA"
when VAR1 = "YYYY" and ID in (101,102,103,106) then "UK"
when VAR1 = "ZZZZ" and ID in (104) then "EUROPE"
when VAR1 = "PPPP" and ID in (102,103,104) then "JAPAN"
when VAR1 = "VVVV" and ID ne . then "ROI"
else VAR1 end as Region,
count(VAR2) as Freq_Month1

from test2
group by Group, Region
order by (case when VAR4 is null then 9999999999999999999999 else VAR4 end), Region
;
quit;
%mend;
%M_Format(case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end);

proc print data=test3;
run;

Current Output:

SAS Output

Obs Group Freq_Month1 1 2 3 4 5 6

0	1
1-1,000	1
1,001-10,000	1
20,001-30,000	1
GT 60,000	1
ND	1

Expected Output:

Obs	Group	Freq_Month1
1	0	1
2	1-1,000	1
3	1,001-10,000	1
4	20,001-30,000	1
5	30,001-40,000	0
6	40,001-50,000	0
7	50,001-60,000	0
8	GT 60,000	1
9	ND	1

[Reeza]

You have to add the missing level in manually if you're using SQL. 

You can use CNTLOUT to generate the table of all values from PROC FORMAT and merge that table in, if you were using a SAS proc, ie proc tabulate you could use the PRELOADFMT option to have all values generated. 

AFAIK there is no way to get this behaviour via PROC PRINT, it has to be done prior to this step. 

[Data_User]

Thanks for your views on this. Can someone explain how can I use PRELOADFMT option in the below code? or may by adding anything before or after this code, but I do not want to change the below code as lots of data needs to be passed through it using macro functions.

%macro M_Format (app);
proc sql;
create table test3 as
select strip(&app.) as Group
,case when VAR1 = "LLLL" and ID in (101) then "USA"
when VAR1 = "MMMM" and ID in (101) then "ASIA"
when VAR1 = "XXXX" and ID in (102) then "AUSTRALIA"
when VAR1 = "YYYY" and ID in (101,102,103,106) then "UK"
when VAR1 = "ZZZZ" and ID in (104) then "EUROPE"
when VAR1 = "PPPP" and ID in (102,103,104) then "JAPAN"
when VAR1 = "VVVV" and ID ne . then "ROI"
else VAR1 end as Region,
count(VAR2) as Freq_Month1

from test2
group by Group, Region
order by (case when VAR4 is null then 9999999999999999999999 else VAR4 end), Region
;
quit;
%mend;
%M_Format(case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end);

[Data_User]

Also please note that, it's not just for one table. I am looking to have all the format values for more than 100 tables & the sample code I have posted here is the exact format in which I am trying to achieve it. Please suggest if there a way to get this done by tweaking that code. Thanks

[collinelliot]

You're going to want to look for "preloadfmt" using proc report. 

[ballardw]

Brief SAS terminology note: Retain means to keep something that is in the data, not to assume a value not present.

Proc Tabulate with the Preloadfmt option will display all of the formatted values as will proc report. Other procedures not so much.

[data_null__]

data test1;
   infile datalines dsd missover dlm="#";
   input VAR1$ VAR2$ VAR3 ID;
   datalines;
LLLL#1#ND#101
MMMM#2#2454#101
XXXX#3#0#102
YYYY#4#195#101
PPPP#6#70000#102
ZZZZ#7#25000#104
;;;;
   run;
proc print;
   run;
Proc format;
   value gfmt(notsorted)
      low-0 = " 0"
      1-1000 = " 1-1,000"
      1001-10000 = " 1,001-10,000"
      10001-20000 = " 10,001-20,000"
      20001-30000 = " 20,001-30,000"
      30001-40000 = " 30,001-40,000"
      40001-50000 = " 40,001-50,000"
      50001-60000 = " 50,001-60,000"
      60001-high = " GT 60,000"
      . = "ND"
      ;
   run;
proc summary nway missing completetypes;
   class var3 / order=data preloadfmt;
   format var3 gfmt.;
   output out=test2;
   run;
proc print;
   run;

[Data_User]

@data_null__ Thanks very much for the workaround. But can we the results like by tweaking the code I have posted here? As I mentioned, lots of tables needs to be created by passing the different fields so I am looking to tweak my code and get the results like you achieve above.

[data_null__]

My output matches you "Expected Output". Your program doesn't make much sense with regards to your expected output.  Why don't you show a representative example of what you have and what you want.

[Data_User]

@data_null__. I understand your code is giving the results as expected. But if you notice there is a macro function in the end in my code [%M_Format(case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end);] through which i have to pass over 100 variables to get the formatted & it's corresponding values. Hence it would be great if we can tweak my code and get the results as expected. Is there a workaround to get it done please?

[ballardw]

Note that Proc tabulate can generate multiple tables with one procedure call as long the variables are used in the same role, class or var, in all table statements and any subsets (where statemen) are the same. Example:

proc tabulate data=sashelp.class;
   class sex age;
   var height weight;
   table sex,
         height*(max min mean std)
   ;
   table age,
         (height weight) *(max mean);
run;

I have some reports using Proc tabulate to generate as many as 15 tables with one procedure call. If the tables are similar it is very easy. And with a few style options can make the appearance very uniform.

[Data_User]

@ballardw. Thanks for looking into it & for your workaround. But I am looking to achieve it by tweaking my code as I have few variables to pass through the macro function defined. Do you have any thoughts on it please?

Thanks

DU

[ballardw]

---

@Data_User wrote:

@ballardw. Thanks for looking into it & for your workaround. But I am looking to achieve it by tweaking my code as I have few variables to pass through the macro function defined. Do you have any thoughts on it please?

Thanks

DU

---

1) describe the Actual purpose of the macro

2) describe the exact output desired by the macro. Since your apparent desire is to have random values inserted into the data then there are LOTS of things that would have to be considered, such as what values are assingned to any other variables involved in the data set

3) describe the variables that may be used how many and what types.

4) describe HOW the resulting data set is to be used.

As a minimum from when I seea parameter like "case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end" I get very concerned about maintainability because passing the start of a sytax element (CASE) without the appropiate complete statement (ending with AS Var insted of just End) it looks very fragile and like the conditions aren't really well determined before writing the macro or process in general. And really have little interest in tweaking such

[Data_User]

@ballardw Please see below. I am getting the results as expected, but the only problem is if there is no raw data then all the format values are not displaying. I want the format values to display with 0 or missing values.

Example:

Current
Field2	Count1	Count2	Count3
"0"	684	203	896
"1-10"	807	537	767
"30-40"	793	834	817
"50-60"	420	892	597
"ND"	745	252	935
Expecting:
Field2	Count1	Count2	Count3
"0"	684	203	896
"1-10"	807	537	767
"10-20"	0	0	0
"20-30"	0	0	0
"30-40"	793	834	817
"40-50"	0	0	0
"50-60"	420	892	597
">60"	0	0	0
"ND"	745	252	935

1) describe the Actual purpose of the macro -

Ans1: To create seperate datasets for each field passing through the macro

2) describe the exact output desired by the macro. Since your apparent desire is to have random values inserted into the data then there are LOTS of things that would have to be considered, such as what values are assingned to any other variables involved in the data set -

Ans2: I have different formats for each variable passing through the macro. Based on the raw values for each field corresponding formats will be applied

3) describe the variables that may be used how many and what types

Ans3: There are over 100 variables & it's combination of character and numeric variables, but the end results will be grouped as per the formats applied.

4) describe HOW the resulting data set is to be used.

Ans4: Resulting dataset will be having groups created through the formats & it's counts, sums, percentages, etc.....

Examples as below:

Output1
Field1	Count1	Count2	Count3
"0"	147	77	199
"1-1,000"	668	815	21
"1,001-10,000"	708	759	522
"10,001-20,000"	660	592	895
"20,001-30,000"	577	422	711
"30,001-40,000"	599	783	187
"40,001-50,000"	801	786	796
"50,001-60,000"	75	567	701
"GT 60,000"	460	512	407
"ND"	964	547	843
Output2
Field2	Count1	Count2	Count3
"0"	931	89	195
">1 <200"	766	813	156
">200 <400"	517	370	299
">400 <600"	757	929	85
">600 <800"	895	470	79
">800 <1000"	315	495	808
"1000+"	258	570	24
"ND"	698	393	255
Output3
Field2	Count1	Count2	Count3
"0"	684	203	896
"1-10"	807	537	767
"10-20"	246	172	940
"20-30"	127	892	301
"30-40"	793	834	817
"40-50"	529	158	866
"50-60"	420	892	597
">60"	81	221	421
"ND"	745	252	935

[Reeza]

If you want to stay in the method you're using, you need to create the empty table and then fill in the values.

1. Create table with all possible values - can extract from your format if desired

2. Join with desired table using a left/right/full join to ensure all values are in the table. You'll need to add some coalesces to make sure the 0's are in the table.  Unfortunately this means you need to have a consistent structure to be able to automate it and ensure that the zero's are set properly. 

Your 'macro' is not dynamic enough to do what you're stating. You have a fixed table name which gets replaced each iteration. 

You may also want to look into PUTC which allows the 'format' to be a text string, then maybe you can avoid some of these CASE statements if you have predefined formats. If it's a table you can automate it, if it's in your head you can't. All the 'region' calculations should also be placed in a table/format for automation instead of hard coding the values. 

Here's a paper that goes over the multiple methods to get the zero rows.

http://www.lexjansen.com/pharmasug/2005/CodersCorner/cc22.pdf 

You're looking at the first option in your case.