Help using Base SAS procedures

Creating a New Data Table With Proc SQL

Reply
Frequent Contributor
Posts: 115

Creating a New Data Table With Proc SQL

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.

Super User
Posts: 9,779

Re: Creating a New Data Table With Proc SQL

Remove  Create table .

Trusted Advisor
Posts: 1,224

Re: Creating a New Data Table With Proc SQL

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;

Super User
Super User
Posts: 7,565

Re: Creating a New Data Table With Proc SQL

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?

Frequent Contributor
Posts: 115

Re: Creating a New Data Table With Proc SQL

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+.

Super User
Posts: 18,589

Re: Creating a New Data Table With Proc SQL

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;

Super User
Super User
Posts: 7,565

Re: Creating a New Data Table With Proc SQL

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

Frequent Contributor
Posts: 115

Re: Creating a New Data Table With Proc SQL

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

Ask a Question
Discussion stats
  • 7 replies
  • 335 views
  • 6 likes
  • 5 in conversation