How do I use user defined format inside a SAS macro

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

How do I use user defined format inside a SAS macro

[ Edited ]

I have created a format like the one below:

 

proc format ;

value $test_group

"23"="a"

"43"="b"

"32"="c"

other="other";

run;

 

I want to use the format test_group for a variable inside a macro. The main problem statement is that I have 10 datasets, all having same variables. I want to assign the above format to the variable in all the datasets at once.

 

Here is the macro:

 

%macro fmt(table=,var1=group_test);

%do;

data temp;

set &table;

length val_fmt $10.;

val_fmt = put(&group_test,$test_group.);/*group_test is the common variable to all datasets that I want to assign the format */

run;

%end;

%mend tname(table=a, var1=group_test);

 

I get the error format not found.

 


Accepted Solutions
Solution
‎09-21-2016 11:52 AM
Respected Advisor
Posts: 4,969

Re: How do I use user defined format inside a SAS macro

If this is the program you are putting into a macro, there is one change to make to the macro.  Your macro refers to:

 

&group_test

 

Instead, it should refer to:

 

&var1

 

The reasons your getting the message about a format are a little tricky, but this should clear them up.  I'm happy to explain further if you would like.

View solution in original post


All Replies
Grand Advisor
Posts: 10,210

Re: How do I use user defined format inside a SAS macro

First verify that you ran the Proc format code and that it created the format okay. (I manage to skip this step about once a month).

Second run you macro with Options mprint symbolgen; and post the resulting log.

 

 

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

700 Options mprint symbolgen;

701 %macro tname(table=,var1=group_test);

702 %do;

703 data temp;

704 set &table;

705 length val_fmt $10.;

706 val_fmt = put(&group_test,$test_group.);/*group_test is the common variable to all datasets

706! that I want to assign the format */

707 run;

708 %end;

709 %mend tname;

710

711 %tname(table=a,var1=group_test);

MPRINT(TNAME): data temp;

SYMBOLGEN: Macro variable TABLE resolves to a

MPRINT(TNAME): set a;

MPRINT(TNAME): length val_fmt $10.;

NOTE 137-205: Line generated by the invoked macro "TNAME".

1 data temp; set &table; length val_fmt $10.; val_fmt = put(&group_test,$test_group.);

-

22

1 ! run;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

a numeric constant, a datetime constant, a missing value, INPUT, PUT.

WARNING: Apparent symbolic reference GROUP_TEST not resolved.

NOTE 137-205: Line generated by the invoked macro "TNAME".

1 data temp; set &table; length val_fmt $10.; val_fmt = put(&group_test,$test_group.);

------------

48

1 ! run;

ERROR 48-59: The format TEST_GROUP was not found or could not be loaded.

MPRINT(TNAME): val_fmt = put(&group_test, $test_group.);

WARNING: Character format specified for the result of a numeric expression.

MPRINT(TNAME): run;

NOTE: Character values have been converted to numeric values at the places given by:

(Line)Smiley SadColumn).

1:65

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.TEMP may be incomplete. When this step was stopped there were 0

observations and 3 variables.

WARNING: Data set WORK.TEMP was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

Respected Advisor
Posts: 4,969

Re: How do I use user defined format inside a SAS macro

If you want to be able to assign a user-defined format inside a macro, you first have to be able to assign a user-defined format outside of a macro.  Pick one of your data sets, and get a program that does what you want.  Then you can worry about how to add macro language to apply those steps to 10 data sets.

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

I did apply the format to a dataset.

data test(keep=var1 val_fmt);

set x.a;

length val_fmt $10.;

val_fmt = put(group_test,$test_group.);

run;

 

This data step works well and the format is applied to the variable.

Solution
‎09-21-2016 11:52 AM
Respected Advisor
Posts: 4,969

Re: How do I use user defined format inside a SAS macro

If this is the program you are putting into a macro, there is one change to make to the macro.  Your macro refers to:

 

&group_test

 

Instead, it should refer to:

 

&var1

 

The reasons your getting the message about a format are a little tricky, but this should clear them up.  I'm happy to explain further if you would like.

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

Oh, Yes. This resolved it. Thanks. If I want to call this macro from another library, I thought the following would work. But it doesnt. Any thoughts? Also, is there other way to apply this to all datasets at once? Something that can loop through all the datasets in a library?

 

proc datasets library=x;

change %tname(table=b,var1=group_test);

run;

 

Grand Advisor
Posts: 10,210

Re: How do I use user defined format inside a SAS macro

Now you are referencing a macro without a definition.

But if it resembles your previous macro FMT you can't have data step code inside a proc datasets call.

Also I believe you would be looking for MODIFY as that is the instruction for modifying the characteristics of variables.

CHANGE renames datasets.

 

Modify datasetname;

   format varaiblename formatname. ;

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

Yes, this macro is the same as the one defined above, fmt.

 

In order to use modify on proc datasets, I need to use the statement- modify dataset_name; inside proc datasets; but I have to repeat the same step 10 times to change all of the 10 datasets.

 

Also, I want to call a macro inside proc datasets and send the dataset name as parameter. Is it possible to do so? Is there any other way?

 

Thanks!

Respected Advisor
Posts: 4,969

Re: How do I use user defined format inside a SAS macro

There are two steps you will need to learn about.

 

First, how do you get the list of all data sets to process?  If you really want to process all that exist in a library, you can query dictionary.tables.  But if  you want to limit the search to those that contain a certain variable, you may have to query dictionary.columns.  There are other ways ... that's just one viable approach.  You might just want to pass a list of data set names as a parameter to the macro, if the list is short enough.

 

Second, once you have a list of data sets to process, how do you get the macro to loop through and apply your DATA step to every data set on the list?  Here's a link that may help with that:

 

http://blogs.sas.com/content/publishing/2015/01/30/sas-authors-tip-getting-the-macro-language-to-per...

 

Each of these steps is probably a little more involved than what you have done so far, but you do have a good first step.

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

The following did the trick for me:

 

%macro tname(tname);

%let var_fmt=group_test;

%do; 

data temp_test;

 set x.&tname; 

 length name $ 30;

 name="&tname";

 length val_fmt $10.;

 val_fmt = put(&var_fmt,$test_group.);

 length member1 3.;

member1= input(var1,3.); /* This conversion doesnt work */

run;

proc append base=one_test data=temp_test force;

run;

%end;

 

%mend tname;

data _null_;

set sashelp.vmember( where=(libname='X' and memtype='DATA'));

call execute('%tname('||strip(memname)||')');

run;

 

 

Here the data step in the end pulls all the datasets from my library and calls the macro. This worked like a charm.

 

However, I am stuck on a very small thing. I am unable to figure out why my conversion from character to numeric  doesnt work. Sometimes I get an error- "The length of numeric variables is 3-8" or the values in the new column, member1 show junk values.

 

When I try to run the same conversion on a character variable, whose length is 16 to a numeric, with same length ( Using input statement,) , I get exponential values in the resultant column. Could you solve this please?

Grand Advisor
Posts: 10,210

Re: How do I use user defined format inside a SAS macro

member1= input(var1,3.); /* This conversion doesnt work */

 

May not  work if var1 exceeds about 65,000 as an integer or contains decimal because you didn't look up what length with Numerics means. Length limits the range of values that may be stored in the specified number of bytes. For most practical purposes the value of limiting the length of numerics has disappeared as the "cost" of data storage has dropped.

 

Another reason you would get bad values is fi var1 exceeds 3 characters. Only the first 3 characters get read into the result. See the result of this code:

data junk;
   var1 = '12345';
   x = input(var1,3.);
run;

x = 123;

 

If you are attempting a generic conversion then you would be better off to 1) not specify the length of a numeric and 2) use a more generic informat such as BEST.

 

Instead of :

length member1 3.;

member1= input(var1,3.);

try

member1 = input(var1, best16.);

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

 

I get the following error,

 

"Data file X.a.DATA is in a format that is native to another host,

or the file encoding does not match the session encoding. Cross Environment Data Access

will be used, which might require additional CPU resources and might reduce performance."

 

Even though I am not manipulating the source table, but the temp table which I am creating in the data step,

 

The data step however, continues to execute and creates the numeric variable, with length 8.

 

P.s: For the error above, I followed the instructions on this link  to check the encoding and they are same.

 

Thanks!

 

Respected Advisor
Posts: 4,969

Re: How do I use user defined format inside a SAS macro

It would be much easier if you came up with a few examples of the output (MEMBER1) and the input (VAR1) that are causing trouble.

 

The error message you got would only occur if you tried to set the length of a numeric to 2.  It shouldn't occur with the code that you posted.  SAS does not store numerics as digits.  SAS uses a totally different format.  So lengths of numerics should only be set to less than 8 when you know the range of values, and when the variable takes on only integer values. 

 

In the case of your program and data, luckily a length of 3 should be long enough to hold all three-digit integers.  If you are getting strange values for MEMBER1, you probably have similarly strange values for VAR1.  For example, if VAR1 contained 5E678, the INPUT function would read "5E6" and intpret this as scientific notation.  The value would be too large to fit into MEMBER1.

Contributor
Posts: 30

Re: How do I use user defined format inside a SAS macro

 

The code above works for the data step on a sample data I have( length=3). However, my actual data has var1 with a value something like "9812802730090070" Type: text Length=16 . My member1 results in 1.3645456E14 , Type: Numeric, Length=8. Var1 does not contain any strange characters.

 

Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 824 views
  • 2 likes
  • 3 in conversation