BookmarkSubscribeRSS Feed
ismahero2
Obsidian | Level 7

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?

7 REPLIES 7
RyanMcGovern
Obsidian | Level 7

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.

 

LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

data_null__
Jade | Level 19

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.

 


 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

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

ballardw
Super User

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1256 views
  • 0 likes
  • 6 in conversation