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

How do I use user defined format inside a SAS macro

vsharipriya — Wed, 21 Sep 2016 15:12:35 GMT

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.

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

ballardw — Wed, 21 Sep 2016 15:19:47 GMT

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.

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

Astounding — Wed, 21 Sep 2016 15:24:16 GMT

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.

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

vsharipriya — Wed, 21 Sep 2016 15:27:53 GMT

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.

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

vsharipriya — Wed, 21 Sep 2016 15:33:13 GMT

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

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

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

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):(Column).

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

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

Astounding — Wed, 21 Sep 2016 15:33:44 GMT

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.

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

vsharipriya — Wed, 21 Sep 2016 15:51:55 GMT

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;

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

ballardw — Wed, 21 Sep 2016 15:58:15 GMT

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

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

vsharipriya — Wed, 21 Sep 2016 16:12:57 GMT

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!

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

Astounding — Wed, 21 Sep 2016 16:13:00 GMT

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-perform-a-do-loop-over-a-list-of-values/

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.

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

vsharipriya — Thu, 22 Sep 2016 19:06:33 GMT

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?

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

ballardw — Thu, 22 Sep 2016 19:45:02 GMT

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

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

vsharipriya — Thu, 22 Sep 2016 20:12:57 GMT

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!

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

Astounding — Thu, 22 Sep 2016 20:13:31 GMT

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.

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

vsharipriya — Thu, 22 Sep 2016 20:20:12 GMT

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!

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

vsharipriya — Thu, 22 Sep 2016 20:38:05 GMT

Again, all these efforts to convert character to numeric is because I cannot use a character variable as an analysis in proc tabulate/ proc report. var1 is my ID and I would like to get the count of Id's for my classification variable. Is there a way I could use character variable in proc tabulate/report for analysis? I would not worry about the conversion then.

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

Astounding — Thu, 22 Sep 2016 20:44:56 GMT

If you assign the variable a length of 3, but end up with a length of 8, that tells me the length is already set. PROC APPEND adds the variable (with a length of 3), but has to expand that to a length of 8 because the base data set that already exists has a length of 8 for the same variable.

The extreme changes in the values aren't explainable based on what you have shown so far.

I can't speak for PROC REPORT, but PROC TABULATE allows you to use both character and numeric variables in the CLASS statement. The VAR statement is limited to numerics only. You would have to check this, but I believe it is possible to get the N statistic (a count) without specifying any VAR variables at all.

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

vsharipriya — Thu, 22 Sep 2016 20:54:56 GMT

This helps. Thanks a lot !

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

ballardw — Thu, 22 Sep 2016 20:59:37 GMT

Appearance for extremely large or small varaibles will end up as exponential notation if you do not specify another format. Please see this code for a very brief example:

data junk;
   x=123456789123458;
run;

proc print;
run;

proc print;
   format x best16.;
run;

Proc tabulate will handle character looking numerics just fine as a CLASS variable. Just make sure that if you specifically request a statistic for them that you only request the N types: N, PCTN, ROWPCTN, COLPCTN and PAGEPCTN.

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

vsharipriya — Thu, 22 Sep 2016 21:17:12 GMT

But in the example I mentioned above, the large value, var1 appears as exponential inspite of specifying the format best16.

%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.);
 member1= input(var1,best16.); /* member1 appears in exponential inspite of specifying the format here */
 run;
 proc append base=one_test data=temp_test force;
 run;
 %end;
 
%mend tname;

And, Proc tabulate I just realized it works fine without using the var statement and just adding it in the class statement. I dont have any statistic that requires numeric value. Just needed the count, hence worked well.