DATA Step, Macro, Functions and more

Formatting question

Reply
Frequent Contributor
Posts: 131

Formatting question

[ Edited ]

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;

Respected Advisor
Posts: 4,920

Re: Formatting question

Posted in reply to Maisha_Huq

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.

PG
Frequent Contributor
Posts: 131

Re: Formatting question

[ Edited ]

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

SAS Employee
Posts: 27

Re: Formatting question

Posted in reply to Maisha_Huq

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).

Super User
Posts: 11,343

Re: Formatting question

Posted in reply to Maisha_Huq

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.

 

SAS Employee
Posts: 27

Re: Formatting question

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;

 

Ask a Question
Discussion stats
  • 5 replies
  • 298 views
  • 1 like
  • 4 in conversation