Hi,
Actually, I guess that you would say that. But in my first question there is a statement like this -> "Even though, If I do the foregoing structure, on Proc Report step there are Suf1vsSuf2 Suf1vsSuf3 Suf2vsSuf3 variables, how can I arrange them with automatically?"
So actually, Proc Report step has already part of my question. I just added Format issue. Also sometimes, I can miss something about my question, minor additions is not okay for you?
I'm asking these question because I don't want to do same mistakes again? Because I use this website frequently and I'm getting many helps from you 🙂
@Ksharp,
I think I almost handled the PROC REPORT step, I just don't understand why comparision columns are coming in color. Even though, I added the following step, it doesn't seem that it was performed in log.
%If %Index(%Upcase(&&var&j),VS)=1 %THEN %DO; %Let varvs&j= Define &&var&j / Display %Str(;); %End;
Data Have;
Length Variable $ 32 Dataset $ 32 Suffix $ 10 Value 8;
Infile Datalines Missover;
Input Variable Dataset Suffix Value;
Datalines;
Variable1 Dataset1 Suffix1 0.70
Variable2 Dataset1 Suffix1 0.40
Variable3 Dataset1 Suffix1 0.80
Variable1 Dataset2 Suffix2 0.45
Variable2 Dataset2 Suffix2 0.65
Variable3 Dataset2 Suffix2 0.35
Variable1 Dataset3 Suffix3 0.85
Variable2 Dataset3 Suffix3 0.90
Variable3 Dataset3 Suffix3 0.40
Variable1 Dataset4 Suffix4 0.85
Variable2 Dataset4 Suffix4 0.90
Variable3 Dataset4 Suffix4 0.40
Variable1 Dataset5 Suffix5 0.85
Variable2 Dataset5 Suffix5 0.90
Variable3 Dataset5 Suffix5 0.40
;
Run;
Proc Sort Data=Have; By Variable; Run;
Proc Transpose Data=Have Out=Want(Drop=_:);
By Variable;
ID Suffix;
Var Value;
Run;
Data Temp;
Set Want;
Array X{*} Suffix1 Suffix2 Suffix3;
Do i=1 To Dim(X)-1;
Do j=i+1 To Dim(X);
V=X{j}-X{i}/X{i};
Name=Catx("_",VName(X{i}),"vs",VName(X{j}));
Output;
End;
End;
Drop i j;
Run;
Proc Transpose Data=Temp Out=Final_Want(Drop=_:);
By Variable Suffix: ;
Var V;
Id name;
Run;
/*I'm getting the Final_Want variables*/
Proc Sql NoPrint;
Select Name Into :VariableName Separated By ' '
From Dictionary.Columns
Where Libname="WORK" And MemName="FINAL_WANT";
Quit;
/*I'm getting the count of variables for next step*/
%Put VariableName=&VariableName.;
%Let Count=%SysFunc(CountW(&VariableName.));
%Put Count=&Count;
Proc Format;
Value Report
Low-<0.46 = "CX00B050"
0.46<-0.8 = "Yellow"
0.8<-High= "Red";
Run;
/*Dynamic structure for Proc Report step*/
%Macro MakeDefine;
Options MPRINT;
%If &Count. GT 0 %Then %Do;
%Do j = 1 %to &Count.;
%Let var&j =%Scan(&VariableName.,&j);
%Put var&j= &&var&j;
%If %Index(%Upcase(&&var&j),VS)=1 %THEN %DO;
%Let varvs&j= Define &&var&j / NoZero Display %Str(;); %End;
%Else %If %Index(%Upcase(&&var&j),VARIABLE)=1 %THEN %DO;
%Let varvs&j= Define &&var&j / NoZero Display Style(Column)=Header %Str(;); %End;
%Else %Do;
%Let varvs&j= Define &&var&j / NoZero Style(Column)={BackGround=Report.} %Str(;); %End;
&&varvs&j;
%End;
%End;
%Else %Do;
%Put ERROR: No value for VariableName specified: &VariableName.;
%End;
%Mend MakeDefine;
/*Perform the macro-mend here*/
Proc Report Data=Final_Want Nowd;
Column &VariableName.;
%MakeDefine;
Run;
But your code has already helped me a lot @Ksharp 🙂
Thank you
... View more
