topic Re: Using Proc SQL in SAS Programming

Using Proc SQL

chimukah — Tue, 27 Oct 2020 15:19:16 GMT

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

Re: Using Proc SQL

Reeza — Tue, 27 Oct 2020 15:22:51 GMT

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

Re: Using Proc SQL

Kurt_Bremser — Tue, 27 Oct 2020 15:24:49 GMT

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

Re: Using Proc SQL

chimukah — Tue, 27 Oct 2020 15:37:57 GMT

Thanks for your suggestion.

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

However, implemented it yet errors as follows:

Re: Using Proc SQL

chimukah — Tue, 27 Oct 2020 16:05:51 GMT

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

Warm regard,
Chimukah