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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.