BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

Hello everyone,

 

Thanks to @Ksharp, I could solve my previous discussion. However, according to my question I have one more question to resolve.

 

My Have data set can change. For example, at the following sample, there are Suffix1 and Suffix2 values but Have data set can include Suffix2 and Suffix3 or Suffix1 and Suffix3 or can include none of them.

 

If whole Suffix1,Suffix2 and Suffix3 are exist then the code works successfully. But If execute the code in this way, I'm getting errors.

For this reason I'm tring to build dynamic structure. 

 

For me Have data set is uncertain, it can change.I need to build this structure depending on posibility of changes Have data set.

 

I need this kind of structure If Suffix1 and Suffix2 variables exist then do; ->(Suffix1-Suffix2)/Suffix2)

I need this kind of structure If Suffix3 and Suffix1 variables exist then do; ->(Suffix3-Suffix1)/Suffix1)

I need this kind of structure If Suffix3 and Suffix2 variables exist then do; ->(Suffix3-Suffix2)/Suffix2)

 

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?

 

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
;
Run;
Proc Sort Data=Have; By Variable; Run;
Proc Transpose Data=Have Out=Want(Drop=_:);
By Variable;
ID Suffix;
Var Value;
Run;

/*Data Want2;*/
/*Set Want;*/
/*Suf1vsSuf2=((Suffix1-Suffix2)/Suffix2);*/
/*Suf1vsSuf3=((Suffix3-Suffix1)/Suffix1);*/
/*Suf2vsSuf3=((Suffix3-Suffix2)/Suffix2);*/
/*Run;*/

Proc Sql;
Create Table Want2 As
Select *
,((Suffix1-Suffix2)/Suffix2) As Suf1vsSuf2
,((Suffix3-Suffix1)/Suffix1) As Suf1vsSuf3
,((Suffix3-Suffix2)/Suffix2) As Suf2vsSuf3
From Want;
Quit;

Proc Report Data=Want2 Nowd;

Column Variable Suffix1 Suffix2 Suffix3 Suf1vsSuf2 Suf1vsSuf3 Suf2vsSuf3;

Define Variable / Display;
Define Suffix1 / Display;
Define Suffix2 / Display;
Define Suffix3 / Display;
Define Suf1vsSuf2 / Display;
Define Suf1vsSuf3 / Display;
Define Suf2vsSuf3 / Display;
Run;

I tried to ask you in this discussion-> https://communities.sas.com/t5/Base-SAS-Programming/Macro-for-PROC-REPORT-to-Meet-Appropriate-Compar... but I learnt it is better to do this over PROC SQl, Now, I'm trying to do over Proc Sql.

 

Can somebody help me, please?

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
OK. Let's fix up your proc report code :


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 temp =%Scan(&VariableName.,&j);
	%If %Index(%Upcase(&temp),_VS_) %THEN %DO;
	 Define &temp / NoZero Display;
	%end;
	%Else %If %Index(%Upcase(temp),VARIABLE) %THEN %DO;
	 Define &temp / NoZero Display Style(Column)=Header;
	%end;
	%Else %Do;
	 Define &temp / NoZero Style(Column)={BackGround=Report.};
    %End;
  %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;

View solution in original post

10 REPLIES 10
Ksharp
Super User
OK. Try this one :


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
;
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{*} suffix:;
 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;


turcay
Lapis Lazuli | Level 10

@Ksharp,

 

Thank you very much, that is invaluable code for me 🙂

 

I just want to do these comparision with between Suffix1,Suffix2 and Suffix3 . So I changed Array statement as below;

 Array X{*} Suffix1 Suffix2 Suffix3;

 

But in my Proc Report step, I have a difficulty to determine the count of variable.

 

There can be more than Suffix1,Suffix2,Suffix3 variables in Have data set. For example, there can be Suffix1,Suffix2,Suffix3, Suffix4 and Suffix5 variables and I need to give format these variables. But for comparsion variables, I don't need any format.

 

My desired result is output of Proc Report

 

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;
Proc Format;
  Value Report  
              Low-<0.46 = "CX00B050"
			  0.46<-0.8 = "Yellow"
			  0.8<-High= "Red";
Run; 
/*I need to build the following structure automatically*/

Proc Report Data=Final_Want Nowd;

Column Variable Suffix1 Suffix2 Suffix3 /*Suffix4 Suffix5*/ 'Suffix1 vs Suffix2'n 'Suffix1 vs Suffix3'n 'Suffix2 vs Suffix3'n ;

Define Variable / Display Style(Column)=Header;
Define Suffix1 / Display Style(Column)={BackGround=Report.};
Define Suffix2 / Display Style(Column)={BackGround=Report.};
Define Suffix3 / Display Style(Column)={BackGround=Report.};
/*Define Suffix4 / Display Style(Column)={BackGround=Report.};*/
/*Define Suffix5 / Display Style(Column)={BackGround=Report.};*/
Define 'Suffix1 vs Suffix2'n / Display;
Define 'Suffix1 vs Suffix3'n / Display;
Define 'Suffix2 vs Suffix3'n  / Display;

Run;

It seems little bit difficult 😞

 

Thank you,

Reeza
Super User

Why do you keep changing the question?

its like you get an answer and it's just one more thing is needed...

 

Try using Suffix: in proc report and/or create a macro variable that counts the number of suffix and use that in your column statement. 

What happens if you include a variable in your column statement but don't have a Define for that variable?

turcay
Lapis Lazuli | Level 10

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 🙂

 

Desired.png

 

Thank you

Reeza
Super User

 

@turcay wrote:

 

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?

  

 

You constantly do this, it shows that you're not thinking your problems through. 

 

A solid naming convention avoids some of the macro's and confusion. Name all your Suffix Columns starting with Suffix and the Comparison Columns with Comp. Use a label to display your label.

 

I also don't recommend allowing spaces in your name, it makes for confusion. For example, your code wouldn't run on my system and I had to make changes, and each step makes it harder to help you.

 

The code below works and will work no matter how many Suffix and Comp columns you have. So it then becomes a data management step above to make sure you have the columns/comparisons you want. I'm not sure why the label isn't showing for the COMP columns but that should hopefully be straightforward to add in. 

 

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;
Proc Format;
  Value Report  
              Low-<0.46 = "CX00B050"
			  0.46<-0.8 = "Yellow"
			  0.8<-High= "Red";
Run; 

data final_want;
set final_want;

rename Suffix1_vs_Suffix2=Comp1
Suffix1_vs_Suffix3=Comp2
Suffix2_vs_Suffix3=Comp3;

label Comp1 = 'Suffix1 vs Suffix2'
      Comp2 = 'Suffix1 vs Suffix3'
      Comp3 = 'Suffix2 vs Suffix3';
run;

Proc Report Data=Final_Want Nowd;

Column Variable Suffix:   comp: ;

Define Variable / Display Style(Column)=Header;
Define Suffix: / Display Style(Column)={BackGround=Report.};
Define Comp: / Display Style(Column)=Header;

Run;

 

 

Reeza
Super User

Results are also coloured appropriately.

Ksharp
Super User
OK. Let's fix up your proc report code :


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 temp =%Scan(&VariableName.,&j);
	%If %Index(%Upcase(&temp),_VS_) %THEN %DO;
	 Define &temp / NoZero Display;
	%end;
	%Else %If %Index(%Upcase(temp),VARIABLE) %THEN %DO;
	 Define &temp / NoZero Display Style(Column)=Header;
	%end;
	%Else %Do;
	 Define &temp / NoZero Style(Column)={BackGround=Report.};
    %End;
  %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;

Reeza
Super User

Do you only ever have the 3 suffixes? Or does that need to be dynamic as well. 

 

turcay
Lapis Lazuli | Level 10

 Hi,

 

I can have more than 3 suffixes but combination of comparisions are just for Suffix1,Suffix2 and Suffix3. @Ksharp method is very useful but I'm just thinking how I can create my Proc Report statement.

 

Thank you.

turcay
Lapis Lazuli | Level 10

Thank you very much @Ksharp, You always post what I need and taught me a lot things. Thank you again 🙂

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
  • 10 replies
  • 1505 views
  • 1 like
  • 3 in conversation