topic Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets in SAS Programming

Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

ertr — Sat, 18 Jun 2016 14:27:33 GMT

Hello everyone,

I try to compare one data set to others. But the data set which I should hold and compare the the others, can change. In my following sample I compare Have1 data set with Have2 and Have3. But what if I need to compare Have2 data set with Have1 and Have3 then how can I build the structure? In my macro variable, data set names are written consecutively so how can I build the dynamic structure? How can I hold Have2 and compare with Have1 and Have3?

My sample code as below;

Data Have1;
Length Variable $ 24 StdDev1 8; 
Infile Datalines Missover;
Input Variable StdDev1;
Datalines;
Variable1 0.96031
Variable2 0.40683
Variable3 0.70029
;
Run;

Data Have2;
Length Variable $ 24 StdDev1 8;  
Infile Datalines Missover;
Input Variable StdDev1 ;
Datalines;
Variable1 0.96480
Variable2 0.41077
Variable3 0.70237
;
Run;

Data Have3;
Length Variable $ 24 StdDev1 8;  
Infile Datalines Missover;
Input Variable StdDev1 ;
Datalines;
Variable1 0.98472
Variable2 0.45315
Variable3 0.68589
;
Run;

%Macro Test(Constant_Label,DataSetLabel);

PROC SQL;
Create Table Comparision_&DataSetLabel. As
Select  T1.Variable,T1.StdDev1,T2.StdDev1 As StdDev2
From &Constant_Label. T1
Left Join &DataSetLabel. T2
On T1.Variable=T2.Variable;
QUIT;

%Mend;
%Macro Loop_Test;
%let i=1;
%do %while(%scan(&Datasets.,&i.+1,%str( ))~=);
%Test(%scan(&Datasets.,1,%str( )),%scan(&Datasets.,&i.+1,%str( )));
%let i=&i.+1;
%end;
%Mend;
%Let Datasets=Have1 Have2 Have3;
%Loop_Test;

Can you help me, please?

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

Cynthia_sas — Sat, 18 Jun 2016 15:15:18 GMT

Hi:

I'm not clear on what you want. I envision something like this:

But that is not clear from what you show. The input data is clear, your desired output is not clear. I'm not sure you need a macro for this and I don't understand the type of comparison you're doing. Your macro program %Loop_Test and your macro program %Test just seem to be doing a repetitive join.

I'd probably just do this in a MERGE with the 3 datasets and do the rename in the MERGE statement which is what I did to get the above output (after adding some extra rows to test for all possible conditions. A program like this could be "macro-ized" so you could pass in a variable number of datasets to the merge, but you have to start with a working SAS program before you "macro-ize" it and I'm not clear on what your working SAS program for your macro is supposed to be doing.

cynthia

data alltogether ;
  length status_flag $20;
  merge have1(in=in1) 
        have2(in=in2 rename=(stddev1=stddev2))
	have3(in=in3 rename=(stddev1=stddev3));
  by variable;
  if in1 and in2 and in3 then status_flag='all3';
  else if in1 and in2 then status_flag= 'in_1_2';
  else if in2 and in3 then status_flag= 'in_2_3';
  else if in3 and in1 then status_flag= 'in_1_3';
  else if in1=1 and in2=0 and in3=0 then status_flag= 'only_in_1';
  else if in1=0 and in2=1 and in3=0 then status_flag= 'only_in_2';
  else if in1=0 and in2=0 and in3=1 then status_flag= 'only_in_3';
  if in1 or in2 or in3 then output alltogether;
run;
  
proc print data=alltogether;
  title 'Using a status_flag to show where data matched on variable';
  var variable stddev1 stddev2 stddev3 status_flag;
run;
title;

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

ertr — Sun, 19 Jun 2016 14:15:55 GMT

@Cynthia_sas,

Sorry for missunderstanding, maybe the following code represents my desired output better. I compare Have1 with both Have2 and Have3, what if I need to compare Have2 with both Have1 and Have3 or what if I need to compare Have3 with both Have1 and Have2.

I can pull the Have1 by using this statement -> %scan(&Datasets.,1,%str( )) but what if I need to pull Have2, what should I do then?

Data Table_Append;
Length Variable	$ 32 StdDev1 8 StdDev2 8 TableName $ 32;
Run;
Proc Sql; Delete From Table_Append;

%Macro Test(Constant_Label,DataSetLabel);

PROC SQL;
Create Table Comparision_&DataSetLabel. As
Select  T1.Variable,T1.StdDev1,T2.StdDev1 As StdDev2
From &Constant_Label. T1
Left Join &DataSetLabel. T2
On T1.Variable=T2.Variable;
QUIT;

Data Comparision2_&DataSetLabel.;
Length TableName $ 32 Variable $ 32;
Set Comparision_&DataSetLabel.;
TableName="&DataSetLabel.";
Run;

Proc Append Base=Table_Append Data=Comparision2_&DataSetLabel.;
Run;

%Mend;
%Macro Loop_Test;
%let i=1;
%do %while(%scan(&Datasets.,&i.+1,%str( ))~=);
%Test(%scan(&Datasets.,1,%str( )),%scan(&Datasets.,&i.+1,%str( )));
%let i=&i.+1;
%end;
%Mend;
%Let Datasets=Have1 Have2 Have3;
%Loop_Test;

%Macro Report;
Proc Report Data= Table_Append Nowd;

Columns Variable TableName ,( StdDev1 StdDev2);
Define Variable / Group Style(Column)=Header ;
Define TableName /"%scan(&Datasets.,1,%str( ))"  Across ;
Define StdDev1 /  Analysis ;
Define StdDev2 / Analysis ;

Run;
%Mend Report;

%Report;

Thank you

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

ertr — Fri, 15 Jul 2016 12:40:38 GMT

@Cynthia_sas hello again,

I re-prepared my code. I used the Have1, Have2 and Have3 data sets which I mentioned in my previous messages.

Let's pretend, the user filled the "Datasets" data set as below. In my sample code comparing calculations are done in Loop_Test loop.

As you can see, I compare the Have1 data set with Have2&Have3 data sets by using following code, however, I want to detect the "Main" value from "Datasets" data set and compare the Have2 data set with Have1&Have3 data sets.

What I mean is that the code should depend on the "Main" value, if Have2 row has "Main" value so Have2 will be my compared data set. if instead of Have2, Have3 row has "Main" value so I will compare the Have1&Have2 data sets with Have3 data set.

Data Datasets;
Length DSname $42 DSlabel $ 25 Label $50;
Infile Cards Dsd Dlm=" ";
Input DSName $ DSLabel Label $;
Cards;
Have1 Comparing1 "Data Set1"
Have2 Main "Data Set2"
Have3 Comparing2 "Data Set3"
;
Run;
 
Proc Sql NoPrint; Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets; Run;
Proc Sql NoPrint; Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets; Run;
Proc Sql NoPrint; Select Label Into: Labels separated by "|" From Datasets; Run;
 
%put &Dataset_Names;
%put &Suffixes;
%put &Labels;
 
%Macro Test(DSConstant,DSName,DSLabel);

PROC SQL;
Create Table Comparision_&DSName. As
Select  T1.Variable
,T1.StdDev1
,T2.StdDev1 As StdDev2
,"&DSLabel." As Dataset
From &DSConstant. T1
Left Join &DSName. T2
On T1.Variable=T2.Variable;
QUIT;

%Mend Test;
%Macro Loop_Test;
%let i=1;
%do %While(%scan(&Dataset_Names.,&i.+1,%str( ))~=);
%Test(%Scan(&Dataset_Names.,1,%str( )),%Scan(&Dataset_Names.,&i.+1,%str( )),%Scan(&Labels.,&i.+1,%str(|)));
%let i=&i.+1;
%end;
%Mend Loop_Test;

%Loop_Test;

My question is related to how can I build these structure?

The following first two data sets are the output of my code but I want to get the latest two output by depending on my foregoing explanations, is it possible?

Thank you

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

ertr — Fri, 15 Jul 2016 12:38:04 GMT

Can somebody help me, please?

If you didn't understand my questions, I can re-explain my questions

Thank you

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

Cynthia_sas — Fri, 15 Jul 2016 13:01:14 GMT

Hi:

Hi:
Nobody can run your code, since you have not provided data. Sometimes if I understand the problem I can make some dummy data that approximates the real data, but in this case, I don't see a variable or dataset called "Main" in your output, I don't know what the highlighted cells mean, I don't know what the red line is intended to represent, and I don't see a clear explanation of input or output.

It seems likely that your screen shot is of the output tables that are created, but I am not sure what is wrong or not working. I see that you have a macro program that looks like it is generating code. The only thing I can see is that your macro generates code. Although I understand that %looptest is building an invocation for %test based on the macro variables you create, without understanding WHAT the output is supposed to be and understanding WHAT The input is. I don't know what you intend for the output. If your intent is to get tables, then without any input data,

As an example, when I try to run your code, I see that your macro code compiles without error, but I get error messages, which leave me unable to go any farther than to comment that the macro program looks like it generates the code you want to generate:


1    Data Datasets;
2    Length DSname $42 DSlabel $ 25 Label $50;
3    Infile Cards Dsd Dlm=" ";
4    Input DSName $ DSLabel Label $;
5    Cards;

NOTE: The data set WORK.DATASETS has 3 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


9    ;
10   Run;
11
12   Proc Sql NoPrint;
12 !                   Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets;
12 !                                                                                             Run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.01 seconds


13   Proc Sql NoPrint;
13 !                   Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets;
13 !                                                                                         Run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


14   Proc Sql NoPrint;
14 !                   Select Label Into: Labels separated by "|" From Datasets;
14 !                                                                             Run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
15
16   %put &Dataset_Names;
HAVE1 HAVE2 HAVE3
17   %put &Suffixes;
COMPARING1 MAIN COMPARING2
18   %put &Labels;
Data Set1|Data Set2|Data Set3
19
20   options mprint symbolgen;
21
22   %Macro Test(DSConstant,DSName,DSLabel);
23
24   PROC SQL;
25   Create Table Comparision_&DSName. As
26   Select  T1.Variable
27   ,T1.StdDev1
28   ,T2.StdDev1 As StdDev2
29   ,"&DSLabel." As Dataset
30   From &DSConstant. T1
31   Left Join &DSName. T2
32   On T1.Variable=T2.Variable;
33   QUIT;
34
35   %Mend Test;
36   %Macro Loop_Test;
37   %let i=1;
38   %do %While(%scan(&Dataset_Names.,&i.+1,%str( ))~=);
39   %Test(%Scan(&Dataset_Names.,1,%str( )),%Scan(&Dataset_Names.,&i.+1,%str( )),%Scan(&Labels.,&i.+1,%str(|)));
40   %let i=&i.+1;
41   %end;
42   %Mend Loop_Test;
43
44   %Loop_Test;
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable LABELS resolves to Data Set1|Data Set2|Data Set3
SYMBOLGEN:  Macro variable I resolves to 1
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:00.47
      cpu time            1.62 seconds


MPRINT(TEST):   PROC SQL;
SYMBOLGEN:  Macro variable DSNAME resolves to HAVE2
SYMBOLGEN:  Macro variable DSLABEL resolves to Data Set2
SYMBOLGEN:  Macro variable DSCONSTANT resolves to HAVE1
SYMBOLGEN:  Macro variable DSNAME resolves to HAVE2
MPRINT(TEST):   Create Table Comparision_HAVE2 As Select T1.Variable ,T1.StdDev1 ,T2.StdDev1 As StdDev2 ,"Data
Set2" As Dataset From HAVE1 T1 Left Join HAVE2 T2 On T1.Variable=T2.Variable;
ERROR: File WORK.HAVE1.DATA does not exist.
ERROR: File WORK.HAVE2.DATA does not exist.
MPRINT(TEST):   QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds

MPRINT(LOOP_TEST):  ;
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable I resolves to 1+1
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable I resolves to 1+1
SYMBOLGEN:  Macro variable LABELS resolves to Data Set1|Data Set2|Data Set3
SYMBOLGEN:  Macro variable I resolves to 1+1


MPRINT(TEST):   PROC SQL;
SYMBOLGEN:  Macro variable DSNAME resolves to HAVE3
SYMBOLGEN:  Macro variable DSLABEL resolves to Data Set3
SYMBOLGEN:  Macro variable DSCONSTANT resolves to HAVE1
SYMBOLGEN:  Macro variable DSNAME resolves to HAVE3
MPRINT(TEST):   Create Table Comparision_HAVE3 As Select T1.Variable ,T1.StdDev1 ,T2.StdDev1 As StdDev2 ,"Data
Set3" As Dataset From HAVE1 T1 Left Join HAVE3 T2 On T1.Variable=T2.Variable;
ERROR: File WORK.HAVE1.DATA does not exist.
ERROR: File WORK.HAVE3.DATA does not exist.
MPRINT(TEST):   QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

MPRINT(LOOP_TEST):  ;
SYMBOLGEN:  Macro variable I resolves to 1+1
SYMBOLGEN:  Macro variable DATASET_NAMES resolves to HAVE1 HAVE2 HAVE3
SYMBOLGEN:  Macro variable I resolves to 1+1+1

What does your input data look like? What does your desired output look like? What is the problem/challenge with the code you posted?

cynthia

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

ertr — Mon, 18 Jul 2016 07:45:15 GMT

Okay @Cynthia_sas,

I'm sorry for confusing you.

Let's say I have a sample code as below.(I copied my previos sample);

Data Datasets;
Length DSname $42 DSlabel $ 25 Label $50;
Infile Cards Dsd Dlm=" ";
Input DSName $ DSLabel Label $;
Cards;
Have1 Comparing1 "Data Set1"
Have2 Main "Data Set2"
Have3 Comparing2 "Data Set3"
;
Run;
 
Proc Sql NoPrint; Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets; Run;
Proc Sql NoPrint; Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets; Run;
Proc Sql NoPrint; Select Label Into: Labels separated by "|" From Datasets; Run;
 
%put &Dataset_Names;
%put &Suffixes;
%put &Labels;

And these macro variables printing following output.

But I want to change these values order depending on "MAIN" value, "MAIN" must be first, depending on "MAIN" its Data Set Name -> "Have2" and its Data Set Label -> "Data Set2" must also be first. The remaining values can ben in the same order.

Here is my desired order;("MAIN" value is keyword)

Can I tell my question much better? I hope I could, if I could not, I will re-try to explain my question.

Thank you very much for sparing time for me 🙂

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

Cynthia_sas — Mon, 18 Jul 2016 17:29:29 GMT

Hi:

If all you want to do is change the order in which your macro variable string is created from the WORK.DATASETS data, then you have 3 choices:

1) specify the "datalines" in a different order

2) make a helper variable that indicates the order you want and change the SQL

3) post process WORK.DATASETS with a program to make sure that you have the order you want before the SQL steps.

I suppose there is a 4th choice. You probably could do some kind of CALL SYMPUTX to make the macro variable inside the DATA step program. But, since I don't understand entirely what this piece of your program has to do with the screen shot fo the output that you posted, I am not going to attempt any code for #3 or #4. But here is #1 and #2.

cynthia

#1 -- just change the order of the INPUT data for the DATALINES:

#2 -- make an INTERNAL_ORD helper variable and modify the SQL:

Re: Dynamic Macro Loop - Hold One Data Set and Compare with Other Data Sets

ertr — Mon, 18 Jul 2016 22:12:00 GMT

@Cynthia_sas,

Thank you very much for detailed information. I can use your second method but my real purpose is that detect the"Main" value then order the "Datasets" data set. Here is the point of my ordering->"Main" will be the first the other values will remain same.

I can understand that you don't understand what this piece of my program has to do with the screen shot of the output that my posted, but it is little bit complicated. I just wanted to ask is there any way to do my desired calculation.

I just want to compare the one data set with other ones depending on "Main" value for my sample. As I said, I want to detect "Main" value then I want to order.

But your second method seems fine, thank you very much for your warm approach.

Data Datasets;
Length DSname $42 DSlabel $ 25 Label $50 Internal_Ord 8;
Infile Cards Dsd Dlm=" ";
Input DSName $ DSLabel Label $ Internal_Ord;
Cards;
Have1 Comparing1 "Data Set1" 1.2
Have2 Main "Data Set2" 1.1
Have3 Comparing2 "Data Set3" 1.3
;
Run;
Proc Sql;
Create Table Datasets2 As
Select *
From Datasets
Order By Internal_Ord;
Quit;
 
Proc Sql NoPrint; Select Upcase(DSname) Into: Dataset_Names separated by " " From Datasets2; Run;
Proc Sql NoPrint; Select Upcase(DSlabel) Into: Suffixes separated by " " From Datasets2; Run;
Proc Sql NoPrint; Select Label Into: Labels separated by "|" From Datasets2; Run;
 
%put &Dataset_Names;
%put &Suffixes;
%put &Labels;