BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aman4SAS
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

16 REPLIES 16
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Aman4SAS
Obsidian | Level 7

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

Kurt_Bremser
Super User

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

Aman4SAS
Obsidian | Level 7

ahhh, Thanks, i was missing that.

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Aman4SAS
Obsidian | Level 7

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

Kurt_Bremser
Super User

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;

Aman4SAS
Obsidian | Level 7

Thanks

Aman4SAS
Obsidian | Level 7

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;

Kurt_Bremser
Super User

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

Aman4SAS
Obsidian | Level 7

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;

Aman4SAS
Obsidian | Level 7

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;

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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