macro array and proc sql

Posts: 35

macro array and proc sql



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";
%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};

proc print data=tsts;


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



Grand Advisor
Posts: 17,325

Re: macro array and proc sql

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

Posts: 35

Re: macro array and proc sql

Ok let me tell you the story. 


I have the following table:




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";


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:




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



Grand Advisor
Posts: 17,325

Re: macro array and proc sql

You actually want a proc transpose.


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

proc transpose data=have out=want prefix=Y;
by region disease week;
id year;
var count;
Respected Advisor
Posts: 4,966

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
  • 3 in conversation