topic Re: Creating a New Data Table With Proc SQL in SAS Procedures

Creating a New Data Table With Proc SQL

Zachary — Wed, 03 Jun 2015 13:53:38 GMT

proc sql noprint;

create table TextForAnalysis as

select distinct TextCommentsColumn into :TextFromExcel separated by ' '

from TextSingleColumn;

quit;

%put &=TextFromExcel &=sqlobs;

I get two warnings. 1. Apparent Symbolic Reference TextFromExcel not resolved & 2. into clause ignored in the create table statement.

But, everything looks ok in terms of the creation of the TextForAnalysis table.

I would like to tighten it up a bit - any and all help is appreciated.

Re: Creating a New Data Table With Proc SQL

Ksharp — Wed, 03 Jun 2015 13:58:22 GMT

Remove Create table .

Re: Creating a New Data Table With Proc SQL

stat_sas — Wed, 03 Jun 2015 14:07:04 GMT

You are trying to create a macro variable not a table. As, Xia mentioned remove the create table. Also this requires a small modification

%put &TextFromExcel &sqlobs;

Re: Creating a New Data Table With Proc SQL

RW9 — Wed, 03 Jun 2015 14:09:19 GMT

Just to add to the above, why would you want to put textfromexcel into a macro variable. This could create a vast string, and macro processing of lists is to my mind not suited to lists. Why not just keep the distinct values in a table?

Re: Creating a New Data Table With Proc SQL

Zachary — Wed, 03 Jun 2015 14:13:52 GMT

Thank you very much. Now I am confused - had no intention at all of creating a macro variable. My intention was to take a table of about 4,000 words that have duplicates and remove the duplicates with the proc sql code. What I would be left with is an alphabetized list of unique words - should come to around 1,000+.

Re: Creating a New Data Table With Proc SQL

Reeza — Wed, 03 Jun 2015 14:31:01 GMT

What does your dataset TEXTSINGLECOLUMN look like, is it a dataset of one word per row with duplicates?

If you need to separate the words into rows thats a different issue - not quite suited to SQL in my opinion.

If so:

proc sql noprint;

create table TextForAnalysis as

select distinct TextCommentsColumn

from TextSingleColumn

order by textcommentscolumn;

quit;

Re: Creating a New Data Table With Proc SQL

RW9 — Wed, 03 Jun 2015 14:42:11 GMT

Then you don't need this bit of code: into :TextFromExcel separated by ' '

Re: Creating a New Data Table With Proc SQL

Zachary — Thu, 04 Jun 2015 15:58:27 GMT

Thank you very much for all of the helpful answers & suggestions.