Reading a SQL tables into a SAS dataset using Proc SQL

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Reading a SQL tables into a SAS dataset using Proc SQL

I need to readin the email adrdess form a sql tables as follows :

proc sql;

  create table lib.set1 as

  select IdNumber, email

  from sqltable.address;

quit;

The question is - how do I read in only the first 50 characters of the email address ?  It is stored in the address table as a character field of 500 characters in length, and I want to restrict the length of the SAS dataset email variable to 50 characters ?


Accepted Solutions
Solution
‎05-06-2015 03:28 AM
Super User
Posts: 3,115

Re: Reading a SQL tables into a SAS dataset using Proc SQL

You could try compressing your temporary dataset to remove blank space when you create it:

create table gengen.datCacheData (compress = yes) as......

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Reading a SQL tables into a SAS dataset using Proc SQL

Substr ()

Data never sleeps
Contributor
Posts: 40

Re: Reading a SQL tables into a SAS dataset using Proc SQL

Thanks Linus.  I tried the below.  The problem is that the temporary dataset created by this process (in the work directory) before the final dataset is created still seems to be treating the email address as a character field of length 500. So the temporary dataset is enormous, even though the final dataset is "sized down" and the email address is character 10 ?

proc sql;

create table lib.email

(IdNumber char(20) ,

  age num format = 9. ,

  email char(10)) ;

quit;

proc sql /*outobs=1000*/;

  insert into lib.email

  select IdNumber, age, substr(email,1,10)

  from gengen.datCacheData;

quit;

Solution
‎05-06-2015 03:28 AM
Super User
Posts: 3,115

Re: Reading a SQL tables into a SAS dataset using Proc SQL

You could try compressing your temporary dataset to remove blank space when you create it:

create table gengen.datCacheData (compress = yes) as......

Contributor
Posts: 40

Re: Reading a SQL tables into a SAS dataset using Proc SQL

Thanks - the compress option provides the best of both worlds

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 242 views
  • 0 likes
  • 3 in conversation