Loop or Array in Proc Sql

[cyanezIBR]

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;

[ballardw]

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;

[Jagadishkatam]

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