topic Re: Macro to short the data in SAS Programming

Macro to short the data

Aman4SAS — Wed, 12 Mar 2014 10:42:35 GMT

proc sort data=short_state_soc22 ; by state; run;

%macro srt();

data wide_state;

set short_state_soc22;

by state;

array soc22{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

retain soc22;

%do i=11 %to 53 %by 2;

%if soc_22 ="&i" %then soc22[occ_&i]=count;

%end;

%if last.state=1 %then output;

run;

%mend srt;

%srt;

I want to chage the dataset wide as State occ_11 occ_13 occ_15 .....

Programe is running but giving missing values.

Can anyone help me on this.

Re: Macro to short the data

RW9 — Wed, 12 Mar 2014 11:02:56 GMT

Do you mean you want to transpose the data out into wide? If so use proc transpose:

proc sort data=a.short_state_soc22 ; by state; run;

proc transpose data=a.short_state_soc22 out=wide prefix=occ_;
by state;
var count;
id soc_22;
idlabel soc_22;
run;

Re: Macro to short the data

Aman4SAS — Wed, 12 Mar 2014 11:08:15 GMT

yes i can do that by transpose , but my question y its showing missing value and y cant we do it by array?

Re: Macro to short the data

Kurt_Bremser — Wed, 12 Mar 2014 11:20:12 GMT

%if soc_22 ="&i" %then soc22[occ_&i]=count;

In this macro statement, soc_22 is not a variable, but a value!

Therefore you are comparing (the string!) soc_22 to "11", "13" and so on, which is never true.

The same goes for

%if last.state

last.state is just a string when the macro executes (long before the data step even is compiled and starts) and the whole construct has no meaning.

Re: Macro to short the data

Aman4SAS — Wed, 12 Mar 2014 11:32:13 GMT

ahhh, Thanks, i was missing that.

Is there any way to resolve it in the same manner.

Re: Macro to short the data

RW9 — Wed, 12 Mar 2014 11:41:56 GMT

Sorry, having issues posting. KurtBremser has pointed out some bits and I think there are other items which are going wrong. Try the below code:

data wide_state (drop=i x);

set a.short_state_soc22;

by state;

array occ{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53;

retain occ;

do i=11 to 53 by 2;

if i=11 then x=1;

else x=x+1;

if soc_22=i then occ{x}=count;

end;

if last.state then output;

run;

Re: Macro to short the data

Kurt_Bremser — Wed, 12 Mar 2014 11:51:06 GMT

What you want is this:

proc sort data=short_state_soc22 ; by state; run;

data wide_state (drop=soc soc_22 count i);

set short_state_soc22;

by state;

array soc22{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

retain soc22;

if first.state then do i = 1 to 22; soc22 = .; end;

soc22[(soc_22-9)/2] = count;

if last.state then output;

run;

Re: Macro to short the data

Aman4SAS — Wed, 12 Mar 2014 11:59:25 GMT

Thanks for ur ans, Ur ans is correct , but just u r not using retain to hold value.

Re: Macro to short the data

Aman4SAS — Wed, 12 Mar 2014 11:59:42 GMT

Thanks

Re: Macro to short the data

Aman4SAS — Wed, 12 Mar 2014 13:01:08 GMT

But if i use in this manner , in this case soc_22 must have value its not a part of marco , is it?

proc sort data=short_state_soc22 ; by state; run;

%macro srt();

data wide_state;

set short_state_soc22;

by state;

array soc22{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

%do i=11 %to 53 %by 2;

retain soc22;

if soc_22 ="&i" %then soc22[occ_&i]=count;

%end;

if last.state=1 %then output;

run;

%mend srt;

%srt;

Re: Macro to short the data

Kurt_Bremser — Wed, 12 Mar 2014 13:20:21 GMT

First, your example has 2 seemingly small but significant errors:

proc sort data=short_state_soc22 ; by state; run;

%macro srt();

data wide_state;

set short_state_soc22;

by state;

array soc22{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

%do i=11 %to 53 %by 2;

retain soc22;

if soc_22 ="&i" %then soc22[occ_&i]=count; *"%then" is wrong, should be "then";

%end;

if last.state=1 %then output; *"%then" is wrong, should be "then";

run;

%mend srt;

%srt;

Remember that the macro facility is just a code generator. What your macro does looks like this:

data wide_state;

set short_state_soc22;

by state;

array soc22{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

retain soc22;

if soc_22 ="11" then soc22[occ_11]=count;

retain soc22;

if soc_22 ="13" then soc22[occ_13]=count;

retain soc22;

if soc_22 ="15" then soc22[occ_15]=count;

retain soc22;

if soc_22 ="17" then soc22[occ_17]=count;

retain soc22;

if soc_22 ="19" then soc22[occ_19]=count;

retain soc22;

if soc_22 ="21" then soc22[occ_21]=count;

retain soc22;

if soc_22 ="23" then soc22[occ_23]=count;

retain soc22;

if soc_22 ="25" then soc22[occ_25]=count;

retain soc22;

if soc_22 ="27" then soc22[occ_27]=count;

retain soc22;

if soc_22 ="29" then soc22[occ_29]=count;

retain soc22;

if soc_22 ="31" then soc22[occ_31]=count;

retain soc22;

if soc_22 ="33" then soc22[occ_33]=count;

retain soc22;

if soc_22 ="35" then soc22[occ_35]=count;

retain soc22;

if soc_22 ="37" then soc22[occ_37]=count;

retain soc22;

if soc_22 ="39" then soc22[occ_39]=count;

retain soc22;

if soc_22 ="41" then soc22[occ_41]=count;

retain soc22;

if soc_22 ="43" then soc22[occ_43]=count;

retain soc22;

if soc_22 ="45" then soc22[occ_45]=count;

retain soc22;

if soc_22 ="47" then soc22[occ_47]=count;

retain soc22;

if soc_22 ="49" then soc22[occ_49]=count;

retain soc22;

if soc_22 ="51" then soc22[occ_51]=count;

retain soc22;

if soc_22 ="53" then soc22[occ_53]=count;

if last.state=1 then output;

run;

Decide for yourself which code is more efficient and readable!

All those ifs eat a lot of CPU power, BTW.

The macro facility lets you create dynamic code. If the code remains static, do it in the data step itself.

Edit: replaced %then with then

Re: Macro to short the data

Tom — Wed, 12 Mar 2014 14:00:24 GMT

So you want to roll the values up into an array. To do what you want you could use this simple data step. Nesting the SET statement inside the DO loop eliminates the need to RETAIN the variables or add an explicit OUTPUT statement to keep only one observation per state.

data want ;

do until (last.state) ;

set dl.short_state_soc22;

by state;

array occ occ_01 - occ_053 ;

if 1<= soc_22 <= dim(occ) then occ(soc_22) = count;

end;

keep state occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23

occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41

occ_43 occ_45 occ_47 occ_49 occ_51 occ_53

;

run;

If you want the columns that do not have counts to be zero instead of missing then you will need to add code to set them to zero. One way is to add another do loop after the first one.

do _n_ = 1 to dim(occ);

occ(_n_) = sum(occ(_n_),0);

end;

I am not sure why are you only want to keep the odd numbers between 11 and 53? If it is because those are the only values in your data then you could make that decision dynamic by using SQL to query the data and generate the list to keep. It could also determine the upper bound on your array.

You could query the data to get the maximum value of SOC_22 and the distinct list of values that are present.

proc sql noprint ;

select max(soc_22) into :max separated by ' '

from dl.short_state_soc22

;

select distinct catx('_','occ',soc_22)

into :keeplist separated by ' '

from dl.short_state_soc22

;

quit;

data want ;

do until (last.state) ;

set dl.short_state_soc22;

by state;

array occ occ_01 - occ_&max;

if 1<= soc_22 <= dim(occ) then occ(soc_22) = count;

end;

keep state &keeplist ;

run;

Re: Macro to short the data

Aman4SAS — Thu, 13 Mar 2014 06:16:12 GMT

Thanks and my apology for taking you too much time,

as u have already solved my problem in ur prior notes, Thanks.

But now my concern is y its giving error

"Array subscript out of range at line 3 column 37" , As per saving time n ur suggestion its should not be use in real time scenario.

but willing to clear error.

%macro srt();

data wide_state;

set short_state_soc22;

by state;

array soc22{22} occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

%do i=11 %to 53 %by 2;

retain soc22;

if soc_22 ="&i" then soc22[occ_&i]=count;

%end;

if last.state then output;

run;

%mend srt;

%srt;

Re: Macro to short the data

Kurt_Bremser — Thu, 13 Mar 2014 06:30:45 GMT

You are indexing the soc22 array with the _contents_ of occ_&i. If that is less than 1 or more than 22 -> poof!

Re: Macro to short the data

Aman4SAS — Thu, 13 Mar 2014 07:15:25 GMT

I am not passing ref , i m directly passing array variable name,

i.e when soc_22 is 11 then soc22[occ_11]= count; /* its always in between 11-53 by 2

%macro srt();

data wide_state;

set short_state_soc22;

by state;

array soc22 occ_11 occ_13 occ_15 occ_17 occ_19 occ_21 occ_23 occ_25 occ_27 occ_29 occ_31 occ_33 occ_35 occ_37 occ_39 occ_41 occ_43 occ_45 occ_47 occ_49 occ_51 occ_53 ;

%do i=11 %to 53 %by 2;

retain soc22;

if soc_22 ="&i" then soc22[occ_&i]=count; *"%then" is wrong, should be "then";

%put &i;

%end;

if last.state then output; *"%then" is wrong, should be "then";

run;

%mend srt;

%srt;

Re: Macro to short the data

Kurt_Bremser — Thu, 13 Mar 2014 07:24:55 GMT

You MUST reference an array element by a NUMERICAL index, NOT by its name outside of the array:

From the SAS documentation:

subscript

specifies the subscript, which can be a numeric constant, the name of a variable whose value is the number, a SAS numeric expression, or an asterisk (*).

In your code, the numerical index is taken from the contents of any of the occ_* variables, which causes the crash when it is not between 1 and 22.

Re: Macro to short the data

Aman4SAS — Thu, 13 Mar 2014 07:30:18 GMT

Okk. Got it, Thanks a lot.