add vars to data sets with macro and call execute. What's wrong with my codes

[owenwqp1]

Hi everyone, my original sas datasets imported from excel do not include the two variables: state and sic but the names of datasets contain the information: the first two letters are state initials and the digits followed are sic codes(I attached my problem, codes and sample data in txt format ). I want to add the two variables to the sas datasets, with state as character variable and sic as numeric. I created a control dataset named datanames with 5 variables as follows: memtype, libname, memname, sic and state. Variables memname, state and sic are all character variables. Due to my browser problem, I could not use the code window, so I have to attached my codes, and sample data in txt format document. I stated my problems in more detail in the txt document. The results of my codes are not as expected but I don't know what's wrong. Wish you could kindly help me out! Regards

[RW9]

Post test data in the form of a datastep in a code window (its the {i} above post area).

For your issue, you do not need to do any of that.  Assume you have three datasets of AC_123, DE_345, WE_111, then you can extract this information, and put them all in one dataset simply by:

data want;
  set ac_123 de_345 we_111 indsname=tmp;
  state=scan(tmp,1,"_");
  sic=scan(tmp,2,"_");
run;

[Tom]

I would assume that it is is because you passed a missing value for the second parameter for your macro calls.We would need to see the actual dataset you ran and/or the generated macro calls. Perhaps like the datasets you are generating you called that variable CODE instead of SIC?

Note that there is no need to add quotes to numeric literals in the generated assignment statement in your macro.

%macro addvar(state,sic);
data &state&sic;
  length state $2 code 8;
  set &state&sic;
  state="&state";
  code=&sic ;
run;
%mend addvar;

Also why did you create macro variables that you did not need?

data _null_;
  set datanames;
  call execute(cats('%nrstr(%addvar)(',state,sic,')'));
run;

[owenwqp1]

@Tom Thank you so much Tom! With the sample codes you gave me in answering my last post, I sucessfully solved the problem of importing excel tables and changing the charater variables into numeric. That saved me a lot of typing work, since I have more than 6000 excel tables of sic3 indutries and later more than 6000 sas datasets and each with around 100 variables. The original data tables in excel were named with the state name together with the sic3 codes(eg. Florida101,Texas201, NewMexico361, Massachusetts311 etc.). Since the state name and sic3 code are included in the excel table names, they are not included as variables in the original excel tables. What I want is to add the state names and sic3 code as two variables to each sas dataset. The codes I posted yesterday is to deal with this problem. I submitted the code you provided me, but sas gives the following error message: 1 + %addvar(FlORIDA101) 22: LINE and COLUMN cannot be determined.; ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT. What's wrong?

[Tom]

Looks like your macro has errors. Turn on the MPRINT option so that you can see the lines of code that the macro call generates. That should help debug the macro.

[ballardw]

---

@owenwqp1 wrote:
@Tom Thank you so much Tom! With the sample codes you gave me in answering my last post, I sucessfully solved the problem of importing excel tables and changing the charater variables into numeric. That saved me a lot of typing work, since I have more than 6000 excel tables of sic3 indutries and later more than 6000 sas datasets and each with around 100 variables. The original data tables in excel were named with the state name together with the sic3 codes(eg. Florida101,Texas201, NewMexico361, Massachusetts311 etc.). Since the state name and sic3 code are included in the excel table names, they are not included as variables in the original excel tables. What I want is to add the state names and sic3 code as two variables to each sas dataset. The codes I posted yesterday is to deal with this problem. I submitted the code you provided me, but sas gives the following error message: 1 + %addvar(FlORIDA101) 22: LINE and COLUMN cannot be determined.; ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT. What's wrong?

---

Did you misspell Florida? Or do you have a dataset named incorrectly?

[owenwqp1]

@Tom Hi Tom, With your suggestion last time, I use the mprint option and tried two alternatives. One with sic a numeric variable and the other, I used sic1 as a charater variable. I attached the mprint information in the attached txt file. I still couldn't find the what's wrong.Could you help me to find out where the problem is? Thanks a lot! Cheers, Owen

[ArtC]

Notice that &state contains both the state and sic codes and that &SIC is null.  take a look at your code that reads the control file and builds these two variables.  make sure that both are correct at that step. Try %PUT &=STATE &=SIC1;

[owenwqp1]

@ ArtC Hi ArtC, After I run the codes I post yesterday, I use %put &=state &=sic1, I found &state resolved to AL and &sic1 resolved to 91. I think the resolution correct(because my last observation for state and sic1 are AL and 91 respectively). And %addvar(state,sic1) is resolved to %addvar(AL91), but &state resolved to AL91 and &sic1 resolved to null. I'm totally at a loss! Thank you for your help! Cheers

[ArtC · Accepted solution]

Your CALL EXECUTE is incorrectly specified.  You have:

data _null_;

	set datanames;	

	call execute(cats('%nrstr(%addvar)(',state,sic1,')'));

run

you need to include a comma between the resolved values of state and sic1.  In part:

,state , ',' , sic1,  without the comma state and sic1 are being appended and passed to the first parameter which is state.

[owenwqp1]

@ArtC Thank you so much Artc, that's the problem.

Best regards,

Owen