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;
@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
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 🙂
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:
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.
-
2757 views
-
3 likes
-
-
