I am trying to convert ID's from 1A,1B,1C,and 1D to 2-001. But I have 66 id's (66A,66B,66C,66D to 2-066) so instead of typing them one at a time like below, is there a way to create a some sort of loop or array? I've tried to look something up but only find things for arrays of columns. Thanks!
PROC SQL;
CREATE TABLE WORK.QUERY_FOR__Study_Data AS
SELECT t1.CISBR_ID,
t1.'Local ID'n,
(CASE
WHEN t1.'Local ID'n like "11%" then "2-011"
WHEN t1.'Local ID'n like "10%" then "2-010"
WHEN t1.'Local ID'n like "1%" then "2-001"
WHEN t1.'Local ID'n like "2%" then "2-002"
WHEN t1.'Local ID'n like "3%" then "2-003"
WHEN t1.'Local ID'n like "4%" then "2-004"
WHEN t1.'Local ID'n like "5%" then "2-005"
WHEN t1.'Local ID'n like "6%" then "2-006"
WHEN t1.'Local ID'n like "7%" then "2-007"
WHEN t1.'Local ID'n like "8%" then "2-008"
WHEN t1.'Local ID'n like "9%" then "2-009"
else "."
end)
Format $Char8. as Subject_ID,
t1.'Conc1 pg/mL'n,
t1.'Conc2 pg/mL'n,
t1.'Mean pg/mL'n,
t1.'Laboratory Code'n,
t1.'CISBR Comments to Investigator'n
FROM WORK.Study_Data t1;
QUIT;
This worked in a data step but couldn't quite get it to work with SQL. There's probably a more elegant approach with pattern matching but I'm not skilled there.
data junk;
input x $ ;
newid=cats('2-',put(input(substr(x,1,anyalpha(X)-1),f3.),z3.));
datalines;
1A
1C
11A
22d
;
run;
could you please try the below code and let me know if it helped you. sorry i could not test it
%macro test;
PROC SQL;
CREATE TABLE WORK.QUERY_FOR__Study_Data AS
SELECT t1.CISBR_ID,
t1.'Local ID'n,
(CASE
%do i = 1 %to 66;
%let j=put(&i,z2.);
WHEN t1.'Local ID'n like "&i.%" then "2-0&j."
%end;
else "."
end)
Format $Char8. as Subject_ID,
t1.'Conc1 pg/mL'n,
t1.'Conc2 pg/mL'n,
t1.'Mean pg/mL'n,
t1.'Laboratory Code'n,
t1.'CISBR Comments to Investigator'n
FROM WORK.Study_Data t1;
QUIT;
%mend;
%test;
Thanks,
Jag
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.