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
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
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
Thanks for your suggestion.
Actually the par_1 & 2 variables were coming from transposed data.
However, implemented it yet errors as follows:
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
Thank you KurtBremser for your candid advice. It really helped.
Warm regard,
Chimukah
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.