Hi all,
I'm new here, as well as I'm quite new on SAS Enterprise Guide. I have a question about what I think should be an IF/THEN-statement, but since I'm still a noob, I don't know how to perform this statement correctly.
I have a column (let's call it COLUMNONE) with thousands of codes and a second column with codes (COLUMNTWO). What I would like to see is that if COLUMNONE contains a certain value, then that value should be replaced with the value stated in COLUMNTWO. If COLUMNONE does not contain that specific value (but something else), then nothing should happen.
Example.
COLUMNONE contains codes like 5.19.XXXXXXX or 6.07.XXXXXXX, where X are random numbers. Now, if COLUMNONE has a code that starts with '5.19.' (what comes on the Xs is not relevant), then I want the code that is stated in COLUMNTWO. If COLUMNONE has a code other then starting with '5.19.' it's fine and no changes are needed.
Can anyone please provide the steps I need to take in order to perform this?? If you need any more information please ask!
Cheers
Try to add an advanced expression in the Expression editor like this:
case
when substr(t1.columnone,1,4)= '5,19' then t1.columntwo
else t1.columnone
end
The query builder will add the "AS newcolumnname" at the end to create your new calculated column
If get you logic correctly:
If you are using the query builder in EG, you need to think SQL case-when-then-else instead. For this, there is a kind of point-and-click interface.
Using progamming with the data step (untested):
data want;
set have;
if substr(columnone,1,4) = '5,19' then columnone = cats(substr(columntwo,1,4),substr(columnsone,5));
run;
Thanks for the quick reply.
Is it also possible to build this through the computed column editor (advanced expression)?
Try to add an advanced expression in the Expression editor like this:
case
when substr(t1.columnone,1,4)= '5,19' then t1.columntwo
else t1.columnone
end
The query builder will add the "AS newcolumnname" at the end to create your new calculated column
Great. Thanks. It works!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.