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

[Ani7]

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.

[Jagadishkatam]

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;

[PeterClemmensen]

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