BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
owenwqp1
Obsidian | Level 7
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
1 ACCEPTED SOLUTION

Accepted Solutions
ArtC
Rhodochrosite | Level 12

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.

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User Tom
Super User

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
Obsidian | Level 7
@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
Super User Tom
Super User

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
Super User

@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
Obsidian | Level 7
@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
Rhodochrosite | Level 12

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
Obsidian | Level 7
@ 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
Rhodochrosite | Level 12

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
Obsidian | Level 7

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

Best regards,

Owen

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
  • 10 replies
  • 1863 views
  • 4 likes
  • 5 in conversation