Proq SQL left join and changes in the table

[Toni2]

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;

[Patrick]

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]

thanks. You are right. i have edited my initial post adding some data - i hope it helps

[maguiremq]

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]

thanks - i have now edited my initial post - please if you can have a look and advise

[Kurt_Bremser · Accepted solution]

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]

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

[Kurt_Bremser]

---

@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]

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]

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 🙂