BookmarkSubscribeRSS Feed
Pandu
Fluorite | Level 6

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
ABCYES
PQRNO
XYZYES
FGHNO
AAA

YES

 

And I have 2 Source tables T1 and T2 as below,

T1
ProductTarget_StatusValue
11111&status10.36
22222&status20.65
33333YES1
44444NO5
55555YES

4

T2
ProductStatuscalc
11111YES2
22222NO3
33333YES1
44444NO2
55555YES

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
ProductFinal_Val
111110.72
222221.95
333331
4444410
5555516

 

 

Any help is much appreciated.

 

Thank you in advance!!

 

Thanks,

pandu

2 REPLIES 2
DaveShea
Lapis Lazuli | Level 10

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.

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 357 views
  • 0 likes
  • 3 in conversation