Using Proc SQL

[chimukah]

Hi All,

I want to use a proc SQL to create a column in my dataset like this

PROC SQL;

CREATE TABLE _DS AS

SELECT USUBJID,

CASE (PAR_1) WHEN (^= '  ') THEN END AS EOSREAS,

CASE (PAR_2) WHEN (^= '  ') THEN END AS  EOTPREAS

FROM _DS1

ORDER BY USUBJID;

QUIT;

I would have done this if i am using data step:

if par_1 ^= '  ' then EOSREAS=PAR_1;
if par_2 ^= '  ' then EOTPREAS=Par_2;

Kindly advise

Rgds

[Reeza]

PROC SQL;

CREATE TABLE _DS AS

SELECT USUBJID,

CASE  WHEN (not missing(par_1)) THEN END AS EOSREAS,

CASE  WHEN (not missing(par_2)) THEN END AS  EOTPREAS

FROM _DS1

ORDER BY USUBJID;

QUIT;

Depending on your data structure a PROC TRANSPOSE or other approach may also be valid, since they can be dynamic and this is hardcoded remapping.

---

@chimukah wrote:

Hi All,

I want to use a proc SQL to create a column in my dataset like this

PROC SQL;

CREATE TABLE _DS AS

SELECT USUBJID,

CASE (PAR_1) WHEN (^= '  ') THEN END AS EOSREAS,

CASE (PAR_2) WHEN (^= '  ') THEN END AS  EOTPREAS

FROM _DS1

ORDER BY USUBJID;

QUIT;

I would have done this if i am using data step:

if par_1 ^= '  ' then EOSREAS=PAR_1;
if par_2 ^= '  ' then EOTPREAS=Par_2;

Kindly advise

Rgds

---

[chimukah]

Thanks for your suggestion.

Actually the par_1 & 2 variables were coming from transposed data.

However, implemented it yet errors as follows:

[Kurt_Bremser · Accepted solution]

proc sql;
create table _ds as
  select
    USUBJID,
    case
      when PAR_1 ^=  '  '
      then  par_11
      else ""
    end as EOSREAS,
    case
      when PAR_2 ^= '  '
      then par_7
      else ""
    end as EOTPREAS
  from _ds1
  order by USUBJID
;
quit;

Recommended reading: SQL Procedure's User Guide

[chimukah]

Thank you KurtBremser for your candid advice. It really helped.

Warm regard,
Chimukah