BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ujjawal
Quartz | Level 8

Hi Experts,

I am building a macro in which i am trying to collapse categories of a categorical variable. All the categories of a variable having less than 5% would be combined into one and name the label with their categories names' separated by"_". The problem arises in IF THEN statement using macro variables.

proc freq data=def;

table DSTRCT_Code / out= abcd;

run;

PROC SQL NOPRINT;

Select catt("'",DSTRCT_Code, "'") INTO : charvarname separated by ","

from abcd

having PERCENT <= 0.01 and DSTRCT_Code is not null;

QUIT;

PROC SQL NOPRINT;

Select DSTRCT_Code INTO : charvarname2 separated by "_"

from abcd

having PERCENT <= 0.01 and DSTRCT_Code is not null;

QUIT;

%put &charvarname.;

%put &charvarname2.;

options symbolgen mlogic;

data def;

length DSTRCT_Code_NEW $32.;

set def;

if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = &charvarname2.;

else DSTRCT_Code_NEW = DSTRCT_Code;

run;

The above highlighted code returns missing values in DSTRCT_Code. Also i need to make sure if the variable name exceeds 32 characters, it should truncate macro variable upto 32 characters. For example, a variable named" District_Code" having categories A, B and C needs to be combined. So the new variable would be created and named "District_code_NEW" would have a category - "A_B_C" against A, B and C categories of District_Code. Otherwise, same value.

Thanks in anticipation!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

You have a good solution, but the problem was never with DSTRCT_Code.  It was always with DSTRCT_Code_NEW. 

After you experimented with this a few times, your experiments had added a numeric variable DSTRCT_Code_NEW to the DEF data set.  Later, SET DEF brought in a numeric variable DSTRCT_Code_NEW.  Your programming statements were (naturally) unable to change the numeric variable into a character variable.

By adding KEEP=, you are no longer bringing in other variables.  So your programming statements are able to create a DSTRCT_Code_NEW as a character variable. 

View solution in original post

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Provide some test data, in the form of a datastep, and some required output.  Personally I never recommend putting lists of values in macro variables due to limitations and the code needed to deal with these.

Scott_C_Moore
Calcite | Level 5

My guess is that you aren't selecting any values into your macro variables. The PERCENT column in the output table is not in decimal form so, 45.55% is not .4555 but 45.55. You are selecting "if <= 0.01" which would be 1% if it were in decimal form but is actually .0001% in the values that show up in the PERCENT column. I doubt you have any values that small so nothing is read into your macro variables.

Astounding
PROC Star

One change you will need to make is to add double quotes:

if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = "&charvarname2.";

else DSTRCT_Code_NEW = DSTRCT_Code;

It wouldn't make sense to have a SAS statement that says

then DISTRCT_CODE_NEW = A_B_C

when there is no such variable as A_B_C.

There are other issues that bear investigating.  Will INTO : remove both leading and trailing blanks from the values of DSTRCT_Code?  (Probably, I haven't checked.)  Does PERCENT go on a scale of 0 to 1, or a scale of 0 to 100?  (I think it's 0 to 100, but again I haven't checked.)

Good luck.

Ujjawal
Quartz | Level 8

I have tried putting double quotes "&charvarname2." but it is not working.

NOTE: Invalid numeric data, DSTRCT_Code='RS79W'.

It seems this line - " if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = &charvarname2.;"  is converting DSTRCT_Code variable into numeric.

PERCENT go on a scale of 0 to 100. I know it should be PERCENT <= 5 in my code. I was testing the code and by mistake, i put the wrong code here.

Sample District Code Field -

DSTRCT_Code

RS797

RS798

RS79W

RS79C

RS797

RS797

RS797

RS798

RS798

Scott_C_Moore
Calcite | Level 5

What appears in the log for the two %put statements?

Ujjawal
Quartz | Level 8

%put &charvarname.; and %put &charvarname2.; returns what i want.

%put &charvarname.; returns 'RS79W', 'RS79C'. And

%put &charvarname2.; returns RS79W_RS79C.

Astounding
PROC Star

I suspect the conversion is happening at a slightly different place.  Namely, you have continued on with the same interactive SAS session.  Earlier runs that omitted the double quotes defined DSTRCT_Code_NEW as numeric within DEF.  Later runs that use SET DEF are locked into having a numeric variable DSTRCT_Code_NEW.

Try starting a new SAS session and see what happens.

kaade
SAS Employee

Ujjawal,

I think it's more of a quoting issue. You need the values for the in operator to be enclosed in quotes. You can get the quotes as part of the macro variable value by formatting them with the $quote. format. Since I made up the data values, I used 25 as the limit for your conditional processing, you can change that to whatever is appropriate for your data.

Here's my code and I hope it's what you want:

data def;
input DSTRCT_Code $ ;
cards;
A
B
D
A
B
D
C
C
A
A
A
B
B
;

proc freq data=def;

table DSTRCT_Code / out= abcd;

run;

proc print data=abcd;
run;

PROC SQL ; * NOPRINT;  ****I took out noprint just to make sure I was getting results;
select dstrct_code format=$quote10.  into :charvarname separated by ','
from abcd

having PERCENT <= 25 and DSTRCT_Code is not null;
QUIT;
%put charvarname=&charvarname;

PROC SQL NOPRINT;

Select DSTRCT_Code INTO : charvarname2 separated by "_"

from abcd

having PERCENT <= 25 and DSTRCT_Code is not null;

QUIT;

Ron_MacroMaven
Lapis Lazuli | Level 10

> Also i need to make sure if the variable name exceeds 32 characters,

> it should truncate macro variable up to 32 characters

%let charvarname = %substr

(&charvarname                                      !,1,32);

%*12345678901234567890123456789012!;

It will be obvious whatr is happening when you view this statement in monospace font

Ujjawal
Quartz | Level 8

I was able to crack it. When i use KEEP option and select only the categorical variable, the code works fine. But when i run it on full dataset, it throws error. "DSTRCT_Code_NEW " has been assigned both numeric and character. In this case, it treats DSTRCT_Code as a numeric variable so it makes DSTRCT_Code_NEW as numeric variable.

The code below works fine with KEEP option.

data def1;

set def (KEEP = DSTRCT_Code) ;

if DSTRCT_Code in(&charvarname.) then DSTRCT_Code_NEW = "&charvarname2.";

else DSTRCT_Code_NEW = DSTRCT_Code;

run;

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

This behavior is native to SAS system and its default type assignment being a SAS NUMERIC variable unless overridden (various techniques possible).

Also, encourage any self-initiated desk-checking with optimal diagnostic output for SAS log by using this OPTION setting:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC /* MPRINT */ ;

Scott Barry

SBBWorks, Inc.

Astounding
PROC Star

You have a good solution, but the problem was never with DSTRCT_Code.  It was always with DSTRCT_Code_NEW. 

After you experimented with this a few times, your experiments had added a numeric variable DSTRCT_Code_NEW to the DEF data set.  Later, SET DEF brought in a numeric variable DSTRCT_Code_NEW.  Your programming statements were (naturally) unable to change the numeric variable into a character variable.

By adding KEEP=, you are no longer bringing in other variables.  So your programming statements are able to create a DSTRCT_Code_NEW as a character variable. 

Ujjawal
Quartz | Level 8

Thanks a ton! This logic makes sense completely. Sure, i will check the same.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13 replies
  • 2888 views
  • 0 likes
  • 7 in conversation