Macro for PROC REPORT to Meet Appropriate Comparisons

[turcay]

Hello everyone,

I have a sample code as below.  My question probably will be found strange to you. You will try to understand the logic the behind of the code. I have to say that the code goes through a process then coming into this shape such as Have1, Have2,Have3 and Have4 data set. If it becomes like Have1, everything is okay, the code works as I want. However, if it becomes like Have2,Have3 or Have4 the result does not seem as I want.

At this point, I want to change this structure from static to dynamic. I thought, if I use macro-mend, maybe I can reach my aim. "Set" suffix values can change, I just want to build structure which is proper with Proc Report match-ups, if the data set doesn't have Set1,Set2,Set3 and Set4 suffixes, then no extra columns will come.

Here is my code;

Data Have1;
Length VariableName $ 50 DataSetSuffix $ 50 Values 8;
Infile Cards;
Input VariableName DataSetSuffix Values;
Cards;
Variable1 Set1 0.2
Variable2 Set1 0.4
Variable3 Set1 0.6
Variable1 Set2 0.3
Variable2 Set2 0.5
Variable3 Set2 0.7
Variable1 Set3 0.4
Variable2 Set3 0.6
Variable3 Set3 0.8
Variable1 Set4 0.5
Variable2 Set4 0.7
Variable3 Set4 1.0
;
Run;
PROC Report Data=Have1 Nowd SpanRows
Style(Summary) = Header {FontWeight=Bold Color=#112277}
Style(Header)=Header [BorderColor=Black BorderWidth=1px VerticalAlign=M]
Style(Column)=[BorderColor=Black BorderWidth=1px] Out=Want;
 
Column ("Variable" VariableName) (DataSetSuffix,(Values))
("Comparison"("Set1 vs. Set2" Set1vsSet2)("Set1 vs. Set3" Set1vsSet3)("Set2 vs. Set4" Set2vsSet4));
 
Define VariableName /Group Center " " Style(Column)=Header 
{Width=60mm Color=#112277 FontWeight=Bold TextAlign=R VerticalAlign=M};
Define DataSetSuffix /Across "Summary" Order=Data ;
Define Set1vsSet2/" " Computed Center Format=10.6 Style(Column)={Width=20mm Textalign=R};
Define Set1vsSet3/ " " Computed Center Format=10.6 Style(Column)={Width=20mm Textalign=R};
Define Set2vsSet4/ " " Computed Center Format=10.6 Style(Column)={Width=20mm Textalign=R} ;
Define Values / ""  Analysis Sum Center Format=10.6 Style(column)={Width=20mm Textalign=R};
 
Compute Set1vsSet2;
Set1vsSet2=(_c2_-_c3_)/_c3_;
EndComp;
 
Compute Set1vsSet3;
Set1vsSet3=(_c5_-_c2_)/_c2_;
EndComp;
 
Compute Set2vsSet4;
Set2vsSet4=(_c5_-_c3_)/_c3_;
EndComp;
Run;
/*Data can come as below*/
Data Have2;
Length VariableName $ 50 DataSetSuffix $ 50 Values 8;
Infile Cards;
Input VariableName DataSetSuffix Values;
Cards;
Variable1 Set1 0.2
Variable2 Set1 0.4
Variable3 Set1 0.6
Variable1 Set2 0.3
Variable2 Set2 0.5
Variable3 Set2 0.7
;
Run;
/*Or data can come as below*/
Data Have3;
Length VariableName $ 50 DataSetSuffix $ 50 Values 8;
Infile Cards;
Input VariableName DataSetSuffix Values;
Cards;
Variable1 Set2 0.3
Variable2 Set2 0.5
Variable3 Set2 0.7
Variable1 Set3 0.4
Variable2 Set3 0.6
Variable3 Set3 0.8
Variable1 Set4 0.5
Variable2 Set4 0.7
Variable3 Set4 1.0
;
Run;
Data Have4;
Length VariableName $ 50 DataSetSuffix $ 50 Values 8;
Infile Cards;
Input VariableName DataSetSuffix Values;
Cards;
Variable1 Set5 0.4
Variable2 Set5 0.6
Variable3 Set5 0.8
Variable1 Set6 0.5
Variable2 Set6 0.7
Variable3 Set6 1.0
;
Run;

If you write Have2,Have3 or Have4 instead of Have1 in Proc Report statement, you can understand my problem, for Have1,Have2 and Have3 data sets, the desired output as below. I think I need macro-mend and macro Do-Loop in the macro-mend statment.

Can somebody help me, please?

Thank you

[Reeza]

Build your comparison and use proc report to display it. Don't try and twist proc report to get it. 

Explain in your logic for Have2 and Have3. Why do you have certain comparisons and not others? 

And I think you mean prefix not suffix?

[Cynthia_sas]

Hi, and in addition to Reeza's suggestion, I don't recommend jumping onto "Macro/Mend" as a possible solution for a program that is not working. The key to making a program work inside a macro context is to make sure that you start with working code. Even if that means writing 4 different programs to start, make sure that you understand for ALL the possible output you want what is the EXACT code that will generate ALL the outputs -- not one of the outputs. I don't recommend looking for macro to fix anything if your program without using macro is wrong.

I understand that for the difference between Have 2 and Have 2 desired, that you want to not show the "empty" comparison columns. But I do not understand the logic behind the difference between your Have 3 Current and your Have 3 Desired -- what is logic for NOT showing the columns under Comparison? What you show as Desired doesn't make sense to me. What happened to your SET1 column in Have3 Current? If Set1 is not in the Have3 data, I don't understand how you can desire it in the Have3 Desired -- if Set 1 is not there, it's not there.

Then for your Have 4 Current versus your Have 4 Desired, again, this seems like a repeat of your Have 2. What is the logic for not showing comparison at all in the Have 4 Desired? You have values in the Set1 vs Set2 under comparison. What is the logic for not showing them.

It doesn't seem to me that you are ready to move into %MACRO and %MEND yet. Until you can produce HAVE2 Desired, HAVE3 Desired and HAVE4 Desired without macro coding, you don't yet understand the correct code that your macro needs to generate.

cynthia

[turcay]

Hello @Cynthia_sas,

Thank you for your response.

Have3 Current brings wrong results, however, Have3 desired brings right results. By the way, I added Set1 in Have3 Desired by mistake.

Actually, the logic is if Set1 and Set2 exist then do the comparision, if Set1 and Set3 exist then do the comparision, if  Set2 and Set4 exist then do comparision,

If none of them exist, then just the print the columns without comparision.

These are constant comparisions, just for these Suffix or Prefix->Set1 and Set2, Set1 and Set3, Set2 and Set4. The code should make the comparisions if they are exist.

Could I make myself clear?

Thank you

[Cynthia_sas]

I am sorry, I don't understand. You said "Have 3 Current brings wrong results, however Have 3 desired brings right results." So, it sounds like you already have written a program that produces Have 3 Desired. Use that code instead of what you're using for Have 3 Current. Show the code that produced Have 3 Desired. If Have 3 Desired was just something that you designed in Word or Excel then you do NOT have a program that "brings the right results" -- you just have a program that creates Have 3 Current and so it does not create the desired results.

You say that the logic is if "Set1 and Set2 exist" -- my question is WHERE -- in the HAVE file, in the SAS library, in WORK? And further you say" If Set1 and Set 2 exist then do the comparison" -- what is doing the comparison? PROC SQL? PROC REPORT? Some other procedure? It's not clear to me what you want to change or which procedure is doing the comparison. It sounds as if you want the PROC REPORT step to be dynamically generated based on what's inside the various HAVE datasets. If that is the case, you need to have working code for the HAVE1 data that generates the desired results; then you need to have working code for the HAVE2 data that generates the desired results; then you need to have working code for the HAVE3 data that generates the desired results; then you need to have working code for the HAVE4 data that generates the desired results. Once you see all the different PROC REPORTS that you might need to generate, then you might be ready then, and only then to work on a Macro program that dynamically generates PROC REPORT code.

cynthia

[turcay]

@Cynthia_sas,

I just copied from output of Have1 to Have3's desired output in Excel. I did not use any code for Have3 desired. I tried to show my aim.

The condition of existing Set1 and Set2 and etc. is depend on Have datasets, if Have datasets include then Proc Report will compare. I  look whether any cahnges between Set1 and Set2 or Set2 and Set4 adn etc.

I think, I need to do this process before Proc Report procedure, thank you very much for your suggestions.

Thank you

[turcay]

@Reeza,

I'm sure you gave me a helpful suggestions but is it possible to tell me more deeply? Maybe, I can understand waht should I need to do?

Generally, I add the "Set" values at the end of the data set in my macro-mend loop, so that is the reason why I wrote suffix. But I want to realize the part of comparision. Do you understand my aim?

Thank you

[Reeza]

Perform your calculations/analysis in a data step or proc sql. 

Use Proc Report to display your results. 

You may be able to make Proc Report work, but you'll be 'bending over backwards' to get it to work when a data step or proc sql will take less time and the code will be easier to understand. 

[Ksharp]

Use format Best.



Define Set1vsSet2/" " Computed Center Format=best10. Style(Column)={Width=20mm Textalign=R};
Define Set1vsSet3/ " " Computed Center Format=best10. Style(Column)={Width=20mm Textalign=R};
Define Set2vsSet4/ " " Computed Center Format=best10. Style(Column)={Width=20mm Textalign=R} ;
Define Values / ""  Analysis Sum Center Format=best10. Style(column)={Width=20mm Textalign=R};
 

[turcay]

Hello @Ksharp,

I added the format Best10. but I still see the empty columns, my purpose is that not showing the empty columns,

When I use the Best10. the output of Have3 as below;

How can it compare Set1 and Set2 while these Set1 does not exist?

When I use the Best10. the output of Have4 as below;

How can it compare Set1 and Set2 while these suffixes de not exist?

Can you help me, please?

[Ksharp]

Try NOZERO option:



Define Set1vsSet2/" " nozero Computed Center Format=10.6 Style(Column)={Width=20mm Textalign=R};
Define Set1vsSet3/ " " nozero Computed Center Format=10.6 Style(Column)={Width=20mm Textalign=R};
Define Set2vsSet4/ " " nozero Computed Center Format=10.6 Style(Column)={Width=20mm Textalign=R} ;

[turcay]

@Ksharp,

It is very useful option thank you very much. I almost reach my aim. It is okay for Have2 but for Have3 and Have4 they bring the comparision which are not exist.

You can see as below;

Do you have an idea what is the reason for?

Thank you

[Ksharp]

Oh, That ACROSS variable is dynamic , therefore you can not do some calculation based on it .
I suggest you form your data nicely via Data Step or SQL . And just use proc report PRINT it .

[turcay]

Okay,

Thank you for your help 🙂

[Cynthia_sas]

Hi:

  But you listed these report variables in the COLUMN statement:

Column ("Variable" VariableName) (DataSetSuffix,(Values))
("Comparison"("Set1 vs. Set2" Set1vsSet2)("Set1 vs. Set3" Set1vsSet3)("Set2 vs. Set4" Set2vsSet4));

So PROC REPORT will put Set1vsSet2 Set11vsSet3 and Set2vsSet4 ALWAYS on the report because they are in the COLUMN statement.

cynthia