Desktop productivity for business analysts and programmers

Permanent Formats for Numeric Values

Reply
Contributor
Posts: 22

Permanent Formats for Numeric Values

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?

Occasional Contributor
Posts: 11

Re: Permanent Formats for Numeric Values

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.

 

Super User
Posts: 5,383

Re: Permanent Formats for Numeric Values

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.

 

Data never sleeps
Super User
Super User
Posts: 7,711

Re: Permanent Formats for Numeric Values

[ Edited ]

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

 

Respected Advisor
Posts: 3,786

Re: Permanent Formats for Numeric Values

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.

 


 

Super User
Super User
Posts: 7,711

Re: Permanent Formats for Numeric Values

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.

Respected Advisor
Posts: 3,786

Re: Permanent Formats for Numeric Values


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.

Super User
Posts: 11,118

Re: Permanent Formats for Numeric Values

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?

Ask a Question
Discussion stats
  • 7 replies
  • 349 views
  • 0 likes
  • 6 in conversation