DATA Step, Macro, Functions and more

Macro to short the data

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

Macro to short the data

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.

Attachment

Accepted Solutions
Solution
‎03-12-2014 07:51 AM
Super User
Posts: 7,761

Re: Macro to short the data

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Macro to short the data

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;

Super Contributor
Posts: 266

Re: Macro to short the data

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

Super User
Posts: 7,761

Re: Macro to short the data

%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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: Macro to short the data

Posted in reply to KurtBremser

ahhh, Thanks, i was missing that.

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

Super User
Super User
Posts: 7,942

Re: Macro to short the data

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;

Super Contributor
Posts: 266

Re: Macro to short the data

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

Solution
‎03-12-2014 07:51 AM
Super User
Posts: 7,761

Re: Macro to short the data

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: Macro to short the data

Posted in reply to KurtBremser

Thanks

Super Contributor
Posts: 266

Re: Macro to short the data

Posted in reply to KurtBremser

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;

Super User
Posts: 7,761

Re: Macro to short the data

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: Macro to short the data

Posted in reply to KurtBremser

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;

Super User
Posts: 7,761

Re: Macro to short the data

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 266

Re: Macro to short the data

Posted in reply to KurtBremser

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;

Super User
Posts: 7,761

Re: Macro to short the data

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 425 views
  • 9 likes
  • 4 in conversation