Thank you both for such a quick response. Its very helpful. Hi rogerjdangelis, First thank you for giving me the initial tip of multi dimensional array, I tried to loop through after every 3 columns for 3 tests but just went off in a total wrong track of using mod operator and setting array counter. Since I started off by importing data and had some vague idea, after looking your solution I was able to put that missing link and will post my code too. Hi Xia keshan (Ksharp), when I ran into some of your solutions, I save them for later reference and spend couple of days sometimes to dig through the ideas, its just amazing how you write at a much deeper level. Thanks for your solution. I do need to go through it again to get the complete picture 🙂 Here's my solution: * only place where userinput is needed if there is more data is locating
the last cell to read the spredsheet data in the RANGE
here it is J7 for 3 patients data;
PROC IMPORT OUT=WORK.in1
DATAFILE="U:\sas code\test.xlsx"
REPLACE;
GETNAMES = YES;
RANGE="sheet1$a1:J7";
USEDATE=NO;
SCANTIME=YES;
RUN;
* obtain all the variable names ie columns in spreadsheet into a dataset cols;
proc sql noprint;
create table cols as
select name
from dictionary.columns
where libname='WORK'
and memname = 'IN1'
;
quit;
* here we declare 2 macros
patnum = number of patients i.e. column name has patient in it
lastcol = last associated column for that patient
;
data _null_ ;
set cols end=last;
if lowcase(name) =: 'patient'
then totpat + 1;
if last then do;
call symput('patnum', totpat);
call symput('lastcol', name) ;
end;
run;
%put &patnum.;
%put &lastcol.;
* declare a 2 dimensional array
first dimension is based on number of patients - macro var patnum
second dimension is constant 3 since we will always have 3 tests;
data in2;
set in1 (firstobs=2);
*based on number of patients 2D array ;
array avars[&patnum., 3] Patient_2 -- &lastcol.;
* declare test names here ;
array atests[3] test1 test2 test3;
* initialize the tests;
do i = 1 to dim(atests);
atests[i] = .;
end;
*for each patient assign the 3 tests ;
do i = 1 to &patnum.;
subject = vname(avars[i,1]);
atests [1] = avars[i,1];
atests [2] = avars[i,2];
atests [3] = avars[i,3];
day = labtests;
* for sorting daynum;
daynum = input(scan(labtests, -1), 2.);
if not missing(day) then
output;
end;
keep subject day test1 test2 test3 daynum;
run;
* Final dataset sorted by daynum;
proc sql;
create table want as
select subject, day,
test1, test2, test3,
daynum
from in2
order by subject, daynum;
quit;
... View more
