topic Re: Formatting question in SAS Programming

Formatting question

Maisha_Huq — Thu, 12 Nov 2015 04:21:50 GMT

Hi,

Would someone have guidance for how to apply formatting in the following scenario?:

I have an input dataset which applies time formats for certain time variables. My understanding is the variables don't retain the their formats when, in a new data set, I create a variable stacking these time variables alongside other-format numeric variables; In other words, I stack different input datasets, where each has the variable, say, "E" on top of each other but where each will have a different formatted input variable for the new variable, "E" being created

Q--> How do I apply formatting so that each of the values within this new data set's created variable is formatted differently and correctly?

More specifically, I apply the following macro below where I start off with an input dataset (&input) with input variable, (&var), and end up with an output dataset (cont_&byvar.&odata.) which has output vars named E, M, H, etc. After stacking a number of these datasets, the final dataset has vars, E/M/H which each have valid values which need to be formatted differently. How would I format the E M H vars in this case?

Many thanks in advance!

%macro cont(input, var, byvar, odata,wgt,where, retain, num, nused);

*The BY var columns;

proc sort data=&input; by &byvar; run;

proc univariate data=&input noprint;

var &var;

weight &wgt;

&where

by &byvar;

output out=st_&odata._&byvar mean=Mean mode=Mode min=Minimum max=Maximum;

run;

proc transpose data=st_&odata._&byvar out=st_&odata._&byvar._tr;

id &byvar;

run;

data st_&odata._&byvar._tr;

retain _NAME_ &retain.;

set st_&odata._&byvar._tr;

if _NAME_="Mean" then num=&num.;

if _NAME_="Mode" then num=&num. +1;

if _NAME_="Minimum" then num=&num. +2;

if _NAME_="Maximum" then num=&num. +3;

if _NAME_ ne "_TYPE_";

drop _LABEL_;

run;

*The N column for checks;

proc freq data=&input;

table &var/missing out=n_&odata;

&where

run;

proc means data=n_&odata noprint;

var count;

output out=ntot_&odata sum=n;

run;

data ntot_&odata (keep= n);

set ntot_&odata;

run;

proc sort data=stat_&odata._&byvar._tr;

by num;

run;

data st_&odata._&byvar;

set st_&odata._&byvar._tr;

if _n_=1 then set ntot_&odata;

rename num=Col1;

run;

*All schools;

proc univariate data=&input noprint;

var &var;

weight &wgt;

output out=stall_&odata mean=Mean mode=Mode min=Minimum max=Maximum;

run;

proc transpose data=stall_&odata out=stall_&odata._tr;

run;

data stall_&odata._tr ;

set stall_&odata._tr;

if _NAME_="Mean" then num=&num.;

if _NAME_="Mode" then num=&num. +1;

if _NAME_="Minimum" then num=&num. +2;

if _NAME_="Maximum" then num=&num. +3;

if _NAME_ ne "_TYPE_";

rename col1=All;

rename num=Col1;

drop _LABEL_;

run;

proc sort data=stall_&odata._tr;

by Col1;

run;

proc sort data=st_&odata._&byvar;

by Col1;

run;

data cont_&byvar.&odata.;

length nused $50.;

retain col1 &retain n nused;

merge st_&odata._&byvar stall_&odata._tr;

by col1;

nused="&nused.";

proc print data= cont_&byvar.&odata.;

run;

%mend;

Re: Formatting question

PGStats — Thu, 12 Nov 2015 04:35:00 GMT

If the question is how to transfer formats from one dataset to another... here is a trick:

data original;
format age height weight e8.;
run;

data myFormattedClass;
if 0 then set original;
set sashelp.class;
run;

proc print; run;

The trick is to name the dataset with the formatted variables without reading it in.

Re: Formatting question

Maisha_Huq — Thu, 12 Nov 2015 04:43:20 GMT

That's helpful, thanks PGstats! My question is more: is it at all possible to permanently attach formats to values so that the format applies even if I stack multple variables with different formats on top of each other to create a new variable?

For context: The goal is to create a table to ODS output. The table's shell, for example is below. To create the table I created an input dataset for each row then stacked. Unfortunately, I end up stacking data sets where each column needs to display values with different formats. How do I apply the different formats?

	*School Sizea*			*percentage of schools*
	*Small*	*Medium*	*Large*	*Elementary Schools*	*Middle Schools*	*High Schools*	*All Schools*
Time Breakfast Service Starts
Mean
Mode
Minimum
Maximum
Length of Breakfast Period
Mean
Mode
Minimum
Maximum

is a case where I'm stacking input datasets to create a table. This table's columns will have values for different

Re: Formatting question

RogerSpeas — Thu, 12 Nov 2015 08:57:09 GMT

You note that you are insert each row into the table, individually. Couldn't you just make all the columns in the row that you are inserting, character values before stacking? Maybe your question is how to apply an assigned format in the PUTN or PUTC function?

You can use the VFORMAT function to recall the assigned format for that column.

data row1;

infile datalines dsd truncover;

input small medium large elem middle high all ;

format small--elem roman6. middle--all z4. ;

datalines;

1,2,3,1,2,3,4

;

run;

data crow1 ;

set row1 ;

array nvars{*} small medium large elem middle high all;

array cvars{*} $ 10 csmall cmedium clarge celem cmiddle chigh call ;

do i=1 to dim(nvars);

cvars(i)=putn( nvars{i} , vformat( nvars{i} ) ) ;

end;

drop small--all ;

run;

I'm a bit of a lazy programmer. If you PROC PRINT the row table, ROW1, the values will appear formatted in the results. Direct the PROC PRINT with ODS to CSV (values would be formatted) and then read the CSV file back in with the data step.

Now get lazy, run a PROC PRINT for each row table using the VAR statement to align column into a single CSV file with the OBS number turned on. Read in the CSV file back into a data set. Delete the rows where OBS='OBS', to remove the header rows generated by each of the PROC PRINTs. (reminiscent of the time before ODS).

Re: Formatting question

ballardw — Fri, 13 Nov 2015 19:05:33 GMT

Some raw data would help but I think you may be working WAY too hard.

If my input data were to look something like

Variable: SchoolType, with values of Elemenatry, Middle and High School

SchoolSize with values of Small, Medium and large

BreakfastStartTime (with a SAS time value)

BreakfastDuration (values in minutes )

with one record per school Then

proc tabulate data=have;

Class SchoolSize Schooltype;

var MealStartTime MealDuration;

table BreakFastStarttime *(mean*f=hhmm. mode=f*hhmm. min*f=hhmm. max*f=hhmm.)

BreakFastDuration*(mean*f=f3. mode=f*f3. min*f=f3. max*f=f3.) ,

SchoolSize SchoolType All=AllSchools

;

run;

Note that ODS output doesn't really care about the column format as the cell gets what it needs if you use the correct procedure.

Re: Formatting question

RogerSpeas — Fri, 13 Nov 2015 20:22:31 GMT

My bad, I thought the objective was different formats on each row...

Obs	Obs	small	medium	large	elem	middle	high	all
1	1	I	II	III	I	0002	0003	0004
4	1	one	two	three	one	two	three	four

data row1;

infile datalines dsd truncover;

input small medium large elem middle high all ;

format small--elem roman6. middle--all z4. ;

datalines;

1,2,3,1,2,3,4

;

run;

data row2;

infile datalines dsd truncover;

input small medium large elem middle high all ;

format _all_ words. ;

datalines;

1,2,3,1,2,3,4

;

run;

ods csv file='c:\temp\schools.csv';

proc print data=row1;

run;

proc print data=row2;

run;

ods csv close;

proc import datafile ='c:\temp\schools.csv' dbms=csv out=stacked replace;

getnames=yes;

run;

proc print data=stacked;

where obs = 1 ;

run;