topic Re: Rename variable names using macro or array in New SAS User

Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 19:16:44 GMT

Hello,

I am writing a program that creates, manipulates, and later combines datasets from numerous CSV files that are all structured differently. I have a program I wrote last year that works. But, since I will be updating the dataset each year as new data is released, I would like to refine the code to make it smaller and more efficient. The image below is an example of the raw data I am working with in the data step below.

Below is a copy of the code I want to work (emphasis on want).

%macro age_gender(year,n);
data age_gender&year;
	infile "&path\Table 1 - Unduplicated Users by Age and Gender &year..csv" firstobs=6 obs=&n dsd;
	input Site :$35. Gender $ AgeUnd_15 Age15_17 Age18_19 Age20_24 Age25_29 Age30_34 Age35_39 Age40_44 AgeOver_44 Total;
	year=&year;
	array varname {*} age: total;
	do i=1 to dim(varname);
		if propcase(gender)='Female' then Fem&varname{i} = varname{i};
			else if propcase(gender)='Male' then Male&varname{i} = varname{i};
			else if propcase(gender)='Unknown' then Unk&varname{i} = varname{i};
			else if propcase(gender)='Total' then Tot&varname{i} = varname{i};
			else if gender='--- Null' then Null&varname{i} = varname{i};
	end;
	drop AgeUnd_15 Age15_17 Age18_19 Age20_24 Age25_29 Age30_34 Age35_39 Age40_44 AgeOver_44 Total;
run;
%mend;
%age_gender(2015,210);
%age_gender(2016,207);
%age_gender(2017,205);

As it is, SAS is not recognizing my use of prefix and array reference to rename variables to categorize by gender. I would greatly appreciate any insights as to how I may correct the syntax for the program I am trying to write, or if there is a better way to do this.

Thanks,

Ted

Re: Rename variable names using macro or array

PaigeMiller — Thu, 07 Mar 2019 19:23:37 GMT

What are the variable names in the SAS data set that has been created?

SAS is not recognizing my use of prefix and array reference to rename variables to categorize by gender.

Why do you say this? What error do you see? Show us the SAS Log by clicking on the {i} icon and pasting the log into the window that appears DO NOT SKIP THIS STEP.

Re: Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 19:33:56 GMT

I am sorry, but for some reason it would not allow me to copy and past into the { i } text box. Below is an image of my log.

Thank you for your help.

Re: Rename variable names using macro or array

PaigeMiller — Thu, 07 Mar 2019 19:44:50 GMT

When you copy and paste the log, you need to copy the text and paste the text (not a screen capture) into the {i} window.

We also need to know your variable names in the SAS data set.

Re: Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 19:49:23 GMT

The { I } window is still not allowing me to cut and paste from my log. The best I could do was attach a screen shot as a picture. I'm sorry for the inconvenience, I don't know why it is not letting me copy the text and paste it in the text box.

The variables names are:

AgeUnd_15,

Age15_17,

Age18_19,

Age20_24,

Age25_29,

Age30_34,

Age35_39,

Age40_44,

AgeOver_44,

Total

Re: Rename variable names using macro or array

PaigeMiller — Thu, 07 Mar 2019 19:54:05 GMT

Well, I don't know why it wouldn't allow text from your SAS log to be pasted, do you get an error message?

This is a problem, as your screen capture is unreadable, to be honest.

Re: Rename variable names using macro or array

ballardw — Thu, 07 Mar 2019 20:49:20 GMT

Fen&varname(I) references an array that apparently has not been created yet. Or at least I do not see anything like:

Array Fen&varname ...

Same for MALE&varname, UNK&varname, Tot&varname and Null&varname. Each array you want to use must have an associated ARRAY definition statement.

Re: Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 21:11:42 GMT

No. It does nothing when I try to paste into it. I am pasting directly to this message below, if this is any more helpful.

2679 %age_gender(2015,210);

SYMBOLGEN: Macro variable YEAR resolves to 2015

SYMBOLGEN: Macro variable PATH resolves to S:\Choose Well Evaluation\Data Management\Project

1\Data Files\DHEC_Data - Copy

SYMBOLGEN: Macro variable YEAR resolves to 2015

SYMBOLGEN: Macro variable N resolves to 210

SYMBOLGEN: Macro variable YEAR resolves to 2015

NOTE: Line generated by the invoked macro "AGE_GENDER".

2 year=&year; array varname {*} age: total; do i=1 to dim(varname); if

2 ! propcase(gender)='Female' then Fem&varname{i} = varname{i}; else if

---

180

2 ! propcase(gender)='Male' then Male&varname{i} = varname{i}; else if propcase

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference VARNAME not resolved.

NOTE: Line generated by the invoked macro "AGE_GENDER".

2 year=&year; array varname {*} age: total; do i=1 to dim(varname); if

2 ! propcase(gender)='Female' then Fem&varname{i} = varname{i}; else if

2 ! propcase(gender)='Male' then Male&varname{i} = varname{i}; else if propcase

----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference VARNAME not resolved.

NOTE: Line generated by the invoked macro "AGE_GENDER".

3 (gender)='Unknown' then Unk&varname{i} = varname{i}; else if

---

180

3 ! propcase(gender)='Total' then Tot&varname{i} = varname{i}; else if gender='---

3 ! Null' then Null&varname{i} = varname{i}; end; drop AgeUnd_15 Age15_17 Age18_19

WARNING: Apparent symbolic reference VARNAME not resolved.

NOTE: Line generated by the invoked macro "AGE_GENDER".

3 (gender)='Unknown' then Unk&varname{i} = varname{i}; else if

3 ! propcase(gender)='Total' then Tot&varname{i} = varname{i}; else if gender='---

---

180

3 ! Null' then Null&varname{i} = varname{i}; end; drop AgeUnd_15 Age15_17 Age18_19

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference VARNAME not resolved.

NOTE: Line generated by the invoked macro "AGE_GENDER".

3 (gender)='Unknown' then Unk&varname{i} = varname{i}; else if

3 ! propcase(gender)='Total' then Tot&varname{i} = varname{i}; else if gender='---

3 ! Null' then Null&varname{i} = varname{i}; end; drop AgeUnd_15 Age15_17 Age18_19

----

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference VARNAME not resolved.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.AGE_GENDER2015 may be incomplete. When this step was stopped there were

0 observations and 4 variables.

WARNING: Data set WORK.AGE_GENDER2015 was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.08 seconds

cpu time 0.06 seconds

Re: Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 21:15:03 GMT

Hi @ballardw ,

I did not realize it was treating each prefix as a different array. I was wanting to the variable referenced in varname{ i } to be assigned to each prefix (Male, Unk, Tot, etc.) as variable names.

Re: Rename variable names using macro or array

Tom — Thu, 07 Mar 2019 21:33:54 GMT

You defined an array using the name VARNAME.

Then later you are using a macro variable that just happens to also have the same name , VARNAME, to generate a reference to a different array whose name starts with the letters FEM.

Did you define that other array?

What value did you think you had put into the macro variable VARNAME?

Note that SAS is saying that the macro variable VARNAME has not been defined.

Re: Rename variable names using macro or array

Tom — Thu, 07 Mar 2019 21:38:01 GMT

If you can copy the text from the SASLOG then you should be able to paste it into the pop-up windows in the forum. Can you paste any text into the pop-up windows of the forum? if not try a different browser (chrome, IE, Safari, Firefox, etc.)

Re: Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 21:44:41 GMT

I am still quite novice with using arrays. I thought it would append the variable name for the current iteration of the array to each prefix. For instance, in the first line of code within the array:

"if propcase(gender)='Female' then Fem&varname{i} = varname{i};"

I thought it would created a variable named "FemAgeUnd_15" after the first variable referenced in the array "AgeUnd_15" and assign it the newly named variable the value of the original variable.

Re: Rename variable names using macro or array

LEINAARE — Thu, 07 Mar 2019 21:58:32 GMT

Hi @Tom, @ballardw, and @PaigeMiller,

Thank you all for your help with this issue. I devised an alternate approach that I believe may work, where I reference a macro program within a macro program. The code I wrote is below, and it appears to work. I think I will go this route for now. Thank you again for your help. I would appreciate your thoughts on this code as well because, if it proves to have created the desired results, I plan to replicate this method throughout.

Thanks,

Ted

%macro rename(pref);
	&pref.AgeUnd_15=AgeUnd_15; &pref.Age15_17=Age15_17; &pref.Age18_19=Age18_19;
	&pref.Age20_24=Age20_24; &pref.Age25_29=Age25_29; &pref.Age30_34=Age30_34;
	&pref.Age35_39=Age35_39; &pref.Age40_44=Age40_44; &pref.AgeOver_44=AgeOver_44;
    &pref.Total=Total;
%mend;
%macro age_gender(year,n);
data age_gender&year;
	infile "&path\Table 1 - Unduplicated Users by Age and Gender &year..csv" firstobs=6 obs=&n dsd;
	input Site :$35. Gender $ AgeUnd_15 Age15_17 Age18_19 Age20_24 Age25_29 Age30_34 Age35_39 Age40_44 AgeOver_44 Total;
	year=&year;
	if propcase(gender)='Female' then %rename(Fem);
	if propcase(gender)='Male' then %rename(Male);
	if propcase(gender)='Unknown' then %rename(Unk);
	if propcase(gender)='Total' then %rename(Tot);
	if gender='--- Null' then %rename(Null);
	drop AgeUnd_15 Age15_17 Age18_19 Age20_24 Age25_29 Age30_34 Age35_39 Age40_44 AgeOver_44 Total;
run;
%mend;
%age_gender(2015,210);
%age_gender(2016,207);
%age_gender(2017,205);