topic Re: Permanent Formats for Numeric Values in SAS Enterprise Guide

Permanent Formats for Numeric Values

ismahero2 — Fri, 12 Feb 2016 13:01:34 GMT

Hi,

How can I apply a pernanent format to my entire project only for numeric variables that have no assigned format. Maybe a set up or a program I can create in the autoexec process flow.

For example: If I create a dataset and in there I have several numeric variables that have no assigned formats. I would like to see the values with comas. Any numeric value without an assigned format I want the system to apply the COMMA format. If there is an assinged format then keep the assigned format.

Is this possible?

Re: Permanent Formats for Numeric Values

RyanMcGovern — Fri, 12 Feb 2016 13:32:05 GMT

Hi,

I am sure there are plenty of ways to do this but I would approach it like

/*DETERMINE WHAT NUMERIC VARIABLES ARE NOT FORMATTED*/

PROC SQL NOPRINT;

SELECT name INTO :VARS_TO_FORMAT SEPARATED BY ' '

FROM SASHELP.VCOLUMN

WHERE LIBNAME="SASHELP" AND MEMNAME="CARS" AND type="num" AND format='';

QUIT;

/*THIS IS THE LIST OF VARIABLES THAT WE WILL APPLY FORMATS TO*/

%PUT &VARS_TO_FORMAT.;

/*APPLY THE FORMATS*/

DATA cars;

SET SASHELP.cars;

FORMAT &VARS_TO_FORMAT. COMMA20.;

RUN;

I hope you can see that you can modify this to be a macro and cycle thru all of the datasets in your projects by referencing the sas dictionary tables and piping the appropriate libnames and dataset names into macros the same way I did with the variable names.

Re: Permanent Formats for Numeric Values

RW9 — Mon, 15 Feb 2016 09:22:14 GMT

**Modified to use proc datasets

I don't know EG particularly well, but I don't think so. You can of course just add onto a datastep to format all numerics such like:

data have;
  a=10387656; b=4534212; c="abc"; 
run;

data _null_;
 set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE" and FORMAT="")); 
 by memname;
 if first.memname then call execute(cat('proc datasets library=',libname,'; modify ',memname,'; format '));
 call execute(' '||name);
 if last.memname then call execute(' comma16.; run; quit;');
run;

Note, with the above if you want to do all datasets in WORK then remove the and memname="HAVE". I would advise caution with this approach however, why is it you have some variables without a format which need a format. Personally I would correct this at source, where these variables are being created - what happens if someone else wants to do something with this piece of work and doesn't want this format for instance.

Re: Permanent Formats for Numeric Values

data_null__ — Fri, 12 Feb 2016 13:55:24 GMT

I too wonder why the OP wants to do this. However it is good to learn techniques for meta data updates.

You've got the right idea but there is no need to re-create the data as you show here. Not only is it inefficient and unnecessary it might also destroy other meta data attributes like SORTEDBY or any existing INDEX(es) and integrity constraints etc.

@RW9 wrote:

I don't know EG particularly well, but I don't think so. You can of course just add onto a datastep to format all numerics such like:
data have;
  a=10387656; b=4534212; c="abc"; 
run;

data _null_;
 set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE" and FORMAT="")); 
 by memname;
 if first.memname then call execute(cat('data ',libname,'.',memname,'; set data ',libname,'.',memname,'; format '));
 call execute(' '||name);
 if last.memname then call execute(' comma16.; run;');
run;
 
Note, with the above if you want to do all datasets in WORK then remove the and memname="HAVE". I would advise caution with this approach however, why is it you have some variables without a format which need a format. Personally I would correct this at source, where these variables are being created - what happens if someone else wants to do something with this piece of work and doesn't want this format for instance.

Re: Permanent Formats for Numeric Values

RW9 — Fri, 12 Feb 2016 13:59:08 GMT

Yes, sure, you could replace the datastep with proc datasets. I tend to forget about that, normally i wouldnt just be changing a format on its own.

Re: Permanent Formats for Numeric Values

LinusH — Fri, 12 Feb 2016 15:19:55 GMT

Looking in in SASHELP.VCOLUMN is a good approach.

But I recommend using PROC DATASETS whan applying formats - then only the metadata portion of the dataset is touched.

By using a data step you will do a table scan and write, could take some time if you have large data sets.

Also, any other metadata on the data set level will be lost, like indexes and constraints.

This method will also update all data present to the current session, not the objects in your project.

Also, are you sure COMMA. is appropriate for all numerical values? I guess that you could have SAS dates, SAS datetimes and percentages, and perhaps user defined formats.

Also, you need write permission on the physical files.

If you share data with other users, make sure that they are ok with your format logic.

Re: Permanent Formats for Numeric Values

ballardw — Fri, 12 Feb 2016 16:15:16 GMT

Do you have any pseudo-numeric values such as social security numbers, phone numbers, client identification numbers or dates like 20160212 that are not SAS date values, that are stored as numeric but don't have a format?

Re: Permanent Formats for Numeric Values

data_null__ — Fri, 12 Feb 2016 23:37:30 GMT

@RW9 wrote:

Yes, sure, you could replace the datastep with proc datasets. I tend to forget about that, normally i wouldnt just be changing a format on its own.

Maybe you should edit your answer.