topic Re: Proq SQL left join and changes in the table in SAS Programming

Proq SQL left join and changes in the table

Toni2 — Tue, 17 May 2022 13:37:08 GMT

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;

Re: Proq SQL left join and changes in the table

Patrick — Tue, 17 May 2022 10:39:17 GMT

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.

Re: Proq SQL left join and changes in the table

maguiremq — Tue, 17 May 2022 12:02:55 GMT

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.

Re: Proq SQL left join and changes in the table

Kurt_Bremser — Tue, 17 May 2022 13:14:23 GMT

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.

Re: Proq SQL left join and changes in the table

Toni2 — Tue, 17 May 2022 13:39:27 GMT

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

Re: Proq SQL left join and changes in the table

Toni2 — Tue, 17 May 2022 13:40:22 GMT

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

Re: Proq SQL left join and changes in the table

Toni2 — Tue, 17 May 2022 13:55:42 GMT

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 🙂

Re: Proq SQL left join and changes in the table

Kurt_Bremser — Tue, 17 May 2022 14:03:30 GMT

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;

Re: Proq SQL left join and changes in the table

Toni2 — Tue, 17 May 2022 15:18:53 GMT

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

Re: Proq SQL left join and changes in the table

Kurt_Bremser — Tue, 17 May 2022 15:34:48 GMT

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