topic Re: How do I create a macro variable that is based on the values of another macro variable? in SAS Programming

How do I create a macro variable that is based on the values of another macro variable?

Ani7 — Tue, 31 Jul 2018 22:50:16 GMT

Hi, new SAS programmer here. The data I have currently looks like this:

State | User_ID | Year | Month

OH | A122 | 2006 | 1

OH | A122 | 2006 | 2

OH | A321 | 2006 | 1

AL | A423 | 2007 | 1

AK | A532 | 2006 | 2

I need to create macro variables for distinct User_IDs as well as their corresponding states. The code I have for this currently looks like this:

proc sql;
	select 			count(distinct User_ID)
	into 		    :NObs
	from 			have;
	select distinct User_ID
	into 		    :customer1-:customer%left(&NObs)
	from 			have;
    select          State
	into 		    :states
	from 	        have;
	;
quit;

The problem with this is that since there are multiple distinct User_IDs per State, the macro variable for state has far more values than the macro variable for User_ID. What I need here is to have a macro variable for State that has the corresponding state value for each User_ID. For example, customer1=A122 so state1=OH; customer2=A321 so state2=OH; customer3=A423 so state3=AL, etc.

There could be customers with the same User_ID but belong to different states which is why I am trying to create the macro variable for State. However, within a state, all User_IDs are the same customer.

Re: How do I create a macro variable that is based on the values of another macro variable?

Jagadishkatam — Sun, 05 Aug 2018 05:49:45 GMT

why dont we remove the distinct from the below code, it should get you the expected result

proc sql;
	select 			count(User_ID)
	into 		    :NObs
	from 			have;
	select distinct User_ID
	into 		    :customer1-:customer%left(&NObs)
	from 			have;
    select          State
	into 		    :states
	from 	        have;
	;
quit;

Re: How do I create a macro variable that is based on the values of another macro variable?

PeterClemmensen — Sun, 05 Aug 2018 06:01:51 GMT

Why do you want to put these into macro variables? What are you going to do with them?