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

Hi and how frustrating to what I thought might be a brilliant shortcut.  I copied the code right out of the SAS book titled 'SAS 9.2 SQL Procedure - User's Guide'.  I'm new to macros but not SQL, can anyone tell me what is wrong with this example and if so, how to get it to work?  I have a large dataset to use it for. Below is just a small example of 5 records, but the code is right from the book.  It's supposed to create a separate dataset for each unique value of a column called type, from the dataset called features.  The feature types are physical, like river, mountain, etc.  Thanks if you can help!

75   LIBNAME COED '\\Dwdufs2\leeju$\EDS\COED';

NOTE: Libref COED was successfully assigned as follows:

      Engine:        V9

      Physical Name: \\Dwdufs2\leeju$\EDS\COED

76   /* Step 1: separate each by the 'type' of feature variable. */

77

78   proc sql;

79       select count(distinct type)

80           into :n

81           from COED.features;

NOTE: Writing HTML Body file: sashtml1.htm

82       select distinct type

83           into :type1 - :type%left(&n)

84           from COED.features;

85   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.20 seconds

      cpu time            0.01 seconds

86   %macro makeds;

87       %do i=1 %to &n;

88           data &&type&i;

89           drop=type;

90               set COED.features;

91               if type="&&type&i";

92           run;

93       %end;

94   %mend makeds;

95   %makeds

ERROR: Variable type has been defined as both character and numeric.

NOTE: Character values have been converted to numeric values at the places given by:

      (Line):(Column).

      1:1

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

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

         observations and 4 variables.

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

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

ERROR: Variable type has been defined as both character and numeric.

NOTE: Character values have been converted to numeric values at the places given by:

      (Line):(Column).

      1:1

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

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

         observations and 4 variables.

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

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

ERROR: Variable type has been defined as both character and numeric.

NOTE: Character values have been converted to numeric values at the places given by:

      (Line):(Column).

      1:1

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

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

         observations and 4 variables.

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

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

1 ACCEPTED SOLUTION

Accepted Solutions
allurai0412
Fluorite | Level 6

hi,

check this step ....i guess you should use as    drop    type   not   drop = type !!!!!

.....

.....

%macro makeds;

87       %do i=1 %to &n;

88           data &&type&i;

89           drop=type;

90               set COED.features;

91 if type="&&type&i";

92           run;

93       %end;

94   %mend makeds;

95   %makeds

View solution in original post

6 REPLIES 6
allurai0412
Fluorite | Level 6

hi,

check this step ....i guess you should use as    drop    type   not   drop = type !!!!!

.....

.....

%macro makeds;

87       %do i=1 %to &n;

88           data &&type&i;

89           drop=type;

90               set COED.features;

91 if type="&&type&i";

92           run;

93       %end;

94   %mend makeds;

95   %makeds

jlee55
Calcite | Level 5

Thanks, Yallu!  You got me one step farther along in this project.  Judy

Tom
Super User Tom
Super User

@YALLU has the answer to why the code isn't working.  You converted the DROP statement to an assignment statement. Since it was BEFORE the SET statement that defined TYPE as a numeric variable, which conflicted with what was read from the dataset.

A little about using INTO clause in SQL.

To create a single macro value from SQL without the leading and tailing spaces you can use the SEPARATED BY clause.

select count(distinct type) into :n separated by ' ' from COED.features;

select distinct type into :type1 - :type&n from COED.features;

If you have SAS 9.3 then you can get use the TRIMMED keyword instead .

select count(distinct type) into :n trimmed from COED.features;

Actually you do not need to use two queries.  If you ask SQL to generate way too many macro variables it will stop when it runs out of data. And you can use the SQLOBS automatic variable to get the count if you need it for something later.

select distinct type into :type1 - :type999999 from COED.features;

%let n=&sqlobs;

In SAS 9.3 you do not even need to type all of the 9's.  Instead just leave off the upper bound macro variable name completely (although this syntax just looks broken to me.)

select distinct type into :type1 -  from COED.features;

%let n=&sqlobs;

jlee55
Calcite | Level 5

Thanks so much Tom, arggh! The most elementary syntax brought me down.  It worked with the example I provided.

If you're still interested in this example, there are 3 other items I'd like to understand with this example.

1. The new datasets are ending up in my SAS WORK library instead of COED. I'm not sure where in this code to redirect the outputs to COED.

2.  When I run it on my larger dataset and I have as values for type, strings that contain numbers (actually county FIPS codes), it won't work, and I do have them assigned as characters.  They are 6 characters in length and look like, 000001, 000003, 000005...000135.

 

64 LIBNAME COED '\\Dwdufs2\leeju$\EDS\COED';

NOTE: Libref COED was successfully assigned as follows:

Engine: V9

Physical Name: \\Dwdufs2\leeju$\EDS\COED

65 /* Step 1: separate each county (by the 'area' variable) into a separate table in order to

65 ! merge match them

66 in a horizontal format rather than the EDS format of vertical. */

67

68 proc sql;

69 select count(distinct cfips)

70 into :n

71 from COED.counties_cleanup;

NOTE: Writing HTML Body file: sashtml3.htm

72 select distinct cfips

73 into :cfips1 - :cfips%left(&n)

74 from COED.counties_cleanup;

75 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.43 seconds

cpu time 0.04 seconds

 

76 options spool;

77 %macro makeds;

78 %do i=1 %to &n;

79 data &&cfips&i;

80 drop cfips;

81 set COED.counties_cleanup;

82 if cfips="&&cfips&i";

83 run;

84 %end;

85 %mend makeds;

86 %makeds

NOTE: Line generated by the macro variable "CFIPS1".

1 000001

------

22

200

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;,

_DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

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

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

observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

3.When I switch it to the county name, it does work, but it uses the county names up to the first space. So if the county is 'ST. CROIX', it takes only 'ST'.  And if the county is 'Fond du Lac', only 'Fond' is returned, and this creates processing errors too.  Quite fussy macro, and wondering if Perl is more up to the task.

 

NOTE: There were 12841 observations read from the data set COED.COUNTIES_CLEANUP.

NOTE: The data set WORK.SHEBOYGAN has 332 observations and 4 variables.

NOTE: The data set WORK.COUNTY has 332 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

This is for the county name ST. CROIX:

ERROR: Libname ST is not assigned.

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

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Thanks, Judy

Tom
Super User Tom
Super User

You need to use valid dataset names, which means that the names cannot start with a digit or have embedded spaces or punctuation characters.  To make the dataset in a particular library you need to use two level names.

So replace:

data &&cfips&i;


with something like:


data COED.county_&&cfips&i;


jlee55
Calcite | Level 5

Tom, that worked great!  Thanks so much for your solution and tutorial in other methods.  I would love to have more training in macros, and SAS 9.3 to work with.

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
  • 6 replies
  • 898 views
  • 7 likes
  • 3 in conversation