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.
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;
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;
yes i can do that by transpose , but my question y its showing missing value and y cant we do it by array?
%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.
ahhh, Thanks, i was missing that.
Is there any way to resolve it in the same manner.
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;
Thanks for ur ans, Ur ans is correct , but just u r not using retain to hold value.
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;
Thanks
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;
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
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;
You are indexing the soc22 array with the _contents_ of occ_&i. If that is less than 1 or more than 22 -> poof!
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.