How can I use a variable name as column name?

[erickdt]

I've a problem here, I need to use the name of the columns depends on my working day, as:

CASE WHEN DU_AUX = 1 THEN B.DU_01
WHEN DU_AUX = 2 THEN B.DU_02

however i want to optimize my code and not depends on my hands to do it, can anyone help me with this problem?

Should be like this:

CASE  WHEN DU_AUX = 1 THEN B.DU_"&01."
            WHEN DU_AUX = 2 THEN B.DU_"&02."

where "&01" should be the value in DU_AUX.

MY COMPLETE SELECT NOWADAYS:

PROC SQL;
CREATE TABLE PARCELADOS_&MESREF._1 (drop = DU_AUX) AS SELECT
A.*
,CASE WHEN DU_AUX = -2 THEN B.DU_003
WHEN DU_AUX = -1 THEN B.DU_002
WHEN DU_AUX = 0 THEN B.DU_001
WHEN DU_AUX = 1 THEN B.DU_01
WHEN DU_AUX = 2 THEN B.DU_02
WHEN DU_AUX = 3 THEN B.DU_03
WHEN DU_AUX = 4 THEN B.DU_04
WHEN DU_AUX = 5 THEN B.DU_05
WHEN DU_AUX = 6 THEN B.DU_06
WHEN DU_AUX = 7 THEN B.DU_07
WHEN DU_AUX = 8 THEN B.DU_08
WHEN DU_AUX = 9 THEN B.DU_09
WHEN DU_AUX = 10 THEN B.DU_10
WHEN DU_AUX = 11 THEN B.DU_11
WHEN DU_AUX = 12 THEN B.DU_12
WHEN DU_AUX = 13 THEN B.DU_13
WHEN DU_AUX = 14 THEN B.DU_14
WHEN DU_AUX = 15 THEN B.DU_15
WHEN DU_AUX = 16 THEN B.DU_16
WHEN DU_AUX = 17 THEN B.DU_17
WHEN DU_AUX = 18 THEN B.DU_18
WHEN DU_AUX = 19 THEN B.DU_19
/* WHEN DU_AUX = 20 THEN B.DU_20*/
END AS RATING
FROM PARCELADOS_V1 AS A
LEFT JOIN PDA.RATING_&MESREF. AS B
ON A.CPFCNPJ = B.CPF_CNPJ;
QUIT;

[Tom]

Why are you using PROC SQL to transpose your data?

Why not just pull out the data and then use PROC TRANSPOSE?

Or use a data step?

data want ;
  set have ;
  array DU [-2:20] du_003-du_001 du_01-du_20 ;
  if -2 <= du_aux <= 20 then rating=du[du_aux];
run;

[Reeza]

Agreeing with Tom, you're using the wrong tool here and making your life harder. Use PROC TRANSPOSE within SAS.

In a different SQL environment you would use PIVOT (or another similar option) so this isn't a really good solution in any language.

[erickdt]

thanks tom!

I'll try explain better now, yesterday i was running out of my head.....
lol

In my database:  PARCELADOS_V1 I've all my clientes that made some purchase and the working day, aproximattly 3000
In my database: PDA.RATING_&MESREF. I've all clients from my database (+60MM) and the rating for each working day, beacause they can change rating between days....

So I need, put the rating in my database PARCELADOS_V1 based on my working day and i think that i cannot use transpose or array 😞

thanks again!

[Reeza]

Your logic doesn't appear to hold:

CASE  WHEN DU_AUX = 1 THEN B.DU_"&01."
            WHEN DU_AUX = 2 THEN B.DU_"&02."

where "&01" should be the value in DU_AUX.

But your code has the following which doesn't match the logic.

CASE WHEN DU_AUX = -2 THEN B.DU_003
WHEN DU_AUX = -1 THEN B.DU_002
WHEN DU_AUX = 0 THEN B.DU_001
WHEN DU_AUX = 1 THEN B.DU_01
WHEN DU_AUX = 2 THEN B.DU_02

[Reeza · Accepted solution]

---

@erickdt wrote:

So I need, put the rating in my database PARCELADOS_V1 based on my working day and i think that i cannot use transpose or array 😞

---

You can do it easily in a data step, no transpose needed actually and it's seems pretty straight forward and dynamic. 

You'll want to modify the KEEP statement int he second data set to keep only the variables you need.

You may need to modify the array index for the issues I identified in my last post or you may need to use an IF condition. I didn't have any data to test on so there are likely small bugs in places but it gives you an idea.

proc sort data=pda.rating_&mesref;
by cpfcnpj;
run;

proc sort data=parcedalos_v1;
by cpf_cnpj;
run;

data want;
merge parcelados_v1 (in=a) pda.rating_&mesref (rename=cpf_cnpj=cpfcnpj /*keep = (du_aux )*/ );
by cpfcnpj;
if a; *left join;
array _d(-2:19) du_003 du_002 du_001 du_01-du_19;

rating = _d(du_aux);

run;

---

@erickdt wrote:

thanks tom!

I'll try explain better now, yesterday i was running out of my head.....
lol

In my database:  PARCELADOS_V1 I've all my clientes that made some purchase and the working day, aproximattly 3000
In my database: PDA.RATING_&MESREF. I've all clients from my database (+60MM) and the rating for each working day, beacause they can change rating between days....

So I need, put the rating in my database PARCELADOS_V1 based on my working day and i think that i cannot use transpose or array 😞

thanks again!

---

[erickdt]

Tom, thanks SO MUCH!!!!!!! I've made some changes! but it works 😄

thanks thanks thanks

DATA WANT (KEEP= CPFCNPJ &VARS. RATING);
MERGE PARCELADOS_V1 (IN=A) PDA.RATING_&MESREF (RENAME=CPF_CNPJ=CPFCNPJ);
BY CPFCNPJ;
IF A;
ARRAY _D(-2:&DU.) DU_003 DU_002 DU_001 DU_01-&MAXDU.;
RATING = _D(DU_AUX);
RUN;