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;
@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!
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;
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!
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
@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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.