macro array and proc sql

Reply
Contributor
Posts: 35

macro array and proc sql

Hello, 

 

I have a question regarding macro arrays in proc sql in sas. I have coded the following code and I am trying to get access to elements of the arrays (myc, myr, mycount). I also need to find out the lenght of arrays.

 

The goal is getting the values of CONDITION_NAME, region and count and set them into another table. 

 

proc sql noprint;
select CONDITION_NAME, region, count
into :myc SEPERATED BY " ",
       :myr SEPERATED BY " ",
       :mycount SEPERATED BY " "
from data.data2016
where region = "&region" and CONDITION_NAME = "&disease";
quit;
%put &myc;
%put &myr;
%put &mycount;

 

I have tried the following code to somehow go throuth the array values but it didnt work.

 


data tsts;
array tmp{*} &myc;

do i=1 to dim(tmp);
print tmp{i};
end;

proc print data=tsts;
run;

 

I am very new ins SAS ans SAS programming and I would be really appriciated if somebody help me. 

 

 

Super User
Posts: 19,105

Re: macro array and proc sql

Can you explain what your trying to do? It's possible there may be an easier method. 

Contributor
Posts: 35

Re: macro array and proc sql

Ok let me tell you the story. 

 

I have the following table:

 

1.png

 

the table above is result in the below query: 

 

proc sql;
create table cascadeRegion as
select CONDITION_NAME as disease, region , cdc_report_year as year, cdc_report_week as week, sum(count) as count from mydata.data2016
where region = "&region" and CONDITION_NAME = "&disease";
quit;

 

Now I need to get the value of the year, week and count and put in the appropriate cell of the another table like the following:

 

2.png

 

I thought that maybe I can set the query result into array and then set the elements of the array into the second table.

 

 

Super User
Posts: 19,105

Re: macro array and proc sql

You actually want a proc transpose.

 

proc sort data=have;
by region disease week year;
run;

proc transpose data=have out=want prefix=Y;
by region disease week;
id year;
var count;
run;
Super User
Posts: 5,362

Re: macro array and proc sql

[ Edited ]

Have you examined the automatic macro variable &SQLOBS?  It probably contains exactly what you are looking for (at least for the lengths of the arrays).

Ask a Question
Discussion stats
  • 4 replies
  • 441 views
  • 2 likes
  • 3 in conversation