topic Re: Loop or Array in Proc Sql in SAS Programming

Loop or Array in Proc Sql

cyanezIBR — Fri, 29 Aug 2014 18:54:10 GMT

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;

Re: Loop or Array in Proc Sql

ballardw — Fri, 29 Aug 2014 19:28:13 GMT

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;

11A

22d

;

run;

Re: Loop or Array in Proc Sql

Jagadishkatam — Sat, 30 Aug 2014 08:24:20 GMT

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;