I have a Control table with following fields. In this table STATUS field's values are changeable between YES/NO.
Example: The project value 'ABC' may contain either 'YES' or 'NO'
Also, the 'Status' field is being as a Macro variable for each project like below,
data _null_;
set &_INPUT.;
if Project in ("ABC") then
call symputx('Status1', Status, 'G');
else if Project in ("PQR") then
call symputx('Status2', Status, 'G');
so on....
;run;
control table | |
Project | Status |
ABC | YES |
PQR | NO |
XYZ | YES |
FGH | NO |
AAA | YES |
And I have 2 Source tables T1 and T2 as below,
T1 | ||
Product | Target_Status | Value |
11111 | &status1 | 0.36 |
22222 | &status2 | 0.65 |
33333 | YES | 1 |
44444 | NO | 5 |
55555 | YES | 4 |
T2 | ||
Product | Status | calc |
11111 | YES | 2 |
22222 | NO | 3 |
33333 | YES | 1 |
44444 | NO | 2 |
55555 | YES | 4 |
Joining Condition between T1 and T2:
T1 & T2 tables are joing on the Keys "T1.PRODUCT = T2.PRODUCT & T1.Target_Status=T2.STATUS" (Left Join with T1 on LHS)
then T3.Final_val=T2.calc*T1.Value
Target Table: T3
Expected Soulution: Based on the above join condition we expect 'Final_Val' field to be calculated as below.
(ie, T3.Final_val = T2.calc * T1.Value)
T3 | |
Product | Final_Val |
11111 | 0.72 |
22222 | 1.95 |
33333 | 1 |
44444 | 10 |
55555 | 16 |
Any help is much appreciated.
Thank you in advance!!
Thanks,
pandu
Hi Pandu,
I'm a bit confused as to what you need help with here. You have defined the two tables and their join condition and the expression to create the variable Final_Val quite well.
I also am a bit lost as to your use of macro in the initial DATA _NULL_. I can understand the code, but I am not sure what you are trying to achieve. I also notice that the first two rows of the data for Table T1 appear to have macro variable references in the Target_Status column. This is not really what I would expect to see in a numeric column's value.
Finally, that little "and so on...." at the end of the DATA _NULL_ suggests to me that you want a macro variable for each row in Control Table, Status1, Status2, Status3, Status4 etc. with each macro variable holding the value of STATUS from its corresponding row in CONTROL_TABLE. If that is what you want, the current hard-coded method will lead you into trouble eventually, you would be better to let SAS create a Statusx macro variable for each row in CONTROL_TABLE however many there are at run time.
The code below shows you how to make the macro variable creation part of your code a bit more robust.
**************************************************************;
*Create the Control Table ;
**************************************************************;
Data CONTROL_TABLE;
Input Project $3.
@5 Status $3.
;
DATALINES;
ABC YES
PQR NO
XYZ YES
FGH NO
AAA YES
;
Run;
**************************************************************;
*Create a macro variable for each row in Control Table. ;
*Now it does not matter how many rows are in the dataset at ;
*run time, we will get them all involved. ;
**************************************************************;
Data _NULL_;
Set Control_Table End=EOF;
*************************************************************;
*Create a StatusN macro variable for the current row. ;
*************************************************************;
Call SymputX(Compress("Status"||_N_), Status, "G");
*************************************************************;
*If we are on the final row of the input dataset, create a ;
*macro variable that knows how many StatusN macro variables ;
*we just created...... ;
*************************************************************;
If EOF Then
Do;
Call SymputX("Number_Of_Status_Macro_Vars", _N_);
End;
Run;
**************************************************************;
*Prove that our macro variables exist by showing them off in ;
*the SASLOG. ;
**************************************************************;
%Macro ShowEmOff;
**************************************************************;
*Loop over all of the our macro variable and display them. ;
**************************************************************;
%Do i=1%To &Number_Of_Status_Macro_Vars;
%Put Macro Variable Status&i resolves to: &&Status&i;
%End;
%Mend ShowEmOff;
%ShowEmOff;
Please post a reply indicating which parts of your code you need assistance with.
Cheers,
Downunder Dave.
There are a few weird things in your example which I'm not sure if I understand them right.
So do I get this right that you're creating these macro variables "&status1", "&status2" and so on solely based on the row number of the observation in your control table?
And do I also get this right that you're then having an actual string value of "&status[n]" in your data in T1.Target_Status?
So basically you could add a Row_No to your control table and then from table T1 left join to your control table over the index number of your macro variable string (1, 2, 3...) for cases where there isn't already a YES/NO value in T1.Target_Status?
Using this set of &status[n] macro variables feels wrong. Try to solve this purely on a data level with join/look-ups. If it's key/value pairs for the lookup then you can also use SAS Formats.
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 25. Read more here about why you should contribute and what is in it for you!
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.