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

i use the proc sql in a macro to merge multiple tables. The code works but i need to  make some adjustments 

 

I want to adjust the code in order the "Report" from the proc sql  to include the below :

 

1) When the Type in PSI_POP has the value "C" I want to add in the variable "N" the text "No KS is estimated"

 

2) In addition, i want to reorder the variables with variable "N" move to the end as "N" stands for Notes

 

thanks

 

data PSI_POP;
   input Variable $ Type $ Period $ PSI 1. N $3.;
   datalines;
var1 N A 1 Y
var2 C A 2 .
var3 N A 1 .
var4 C A 2 .
;
run;

data KS_POP;
     input Variable $ Type $ Period $ KS 1. N $3.;
     datalines;
var1 N A 0 .
var2 C A 3 .
var4 C A 1 .
;
run;


proc sql;
create table report  as
 select * from PSI_POP as x left join KS_POP as y
 on x.Variable =y.Variable and x.Period =y.Period;
quit;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

So you join on variable and period, but you have two more columns in common: type and n.

Which of these should be used in the query?

Assuming that you want them from the psi_pop table, here's how to do it:

proc sql;
create table report as
  select
    p.variable,
    p.type,
    p.period,
    p.psi,
    k.ks,
    case
      when p.type = "C"
      then "No KS is estimated"
      else p.n
    end as N
  from psi_pop p left join ks_pop k
  on p.variable = k.variable and p.period = k.period
;
quit;

View solution in original post

9 REPLIES 9
Patrick
Opal | Level 21

Even if you would provide the perfect and exhaustive description of what you have and what you need, it would still be hard for many of us to provide you with actually working code without sample data allowing us to first test what we propose.

Please provide sample data created in the form of working SAS data steps and then show us the desired result using this sample data.

Toni2
Lapis Lazuli | Level 10
thanks. You are right. i have edited my initial post adding some data - i hope it helps
maguiremq
SAS Super FREQ

You could use a `CASE WHEN` statement to get what you want, but as @Patrick said, we need example data before we can even provide you an accurate answer.

 

case when x.type = 'Char' then 'No KS is estimated' else '' end as Notes

You also can select the variables in the order that you want them, and then insert the `CASE WHEN` as the last statement before your `FROM` clause.

 

Again, I'm not confident in my answer without example data.

 

This macro converts your existing data into a DATA step that you can post with the SAS code button in your posts. Make sure there's nothing confidential, or simulate your data somehow.

Toni2
Lapis Lazuli | Level 10
thanks - i have now edited my initial post - please if you can have a look and advise
Kurt_Bremser
Super User

So you join on variable and period, but you have two more columns in common: type and n.

Which of these should be used in the query?

Assuming that you want them from the psi_pop table, here's how to do it:

proc sql;
create table report as
  select
    p.variable,
    p.type,
    p.period,
    p.psi,
    k.ks,
    case
      when p.type = "C"
      then "No KS is estimated"
      else p.n
    end as N
  from psi_pop p left join ks_pop k
  on p.variable = k.variable and p.period = k.period
;
quit;
Toni2
Lapis Lazuli | Level 10
thanks, quick questions, how can i add an additional case when in the above?
Kurt_Bremser
Super User

@Toni2 wrote:
thanks, quick questions, how can i add an additional case when in the above?

Like any other item in the SELECT. The CASE-END block is an expression like a calculation or a variable name.

Kurt_Bremser
Super User

Since using the asterisk in a join like yours is VERY BAD CODING STYLE (as reflected by the WARNING in the log), you must replace it with the comprehensive list of variables anyway, and this allows you to do the change in variable order.

 

For question one, use a CASE Expression.

 

Toni2
Lapis Lazuli | Level 10
hi thanks for your comments

For the warning i added the list with variables and works.

For my question one, my problem is i don't know how to combine "left join" with "case" in one SQL 🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 9 replies
  • 831 views
  • 6 likes
  • 4 in conversation