topic Re: How to read text file data into Single column in SAS in SAS Programming

How to read text file data into Single column in SAS

Banu — Thu, 21 Sep 2023 13:35:47 GMT

Hi Everyone,

I have a requirement to read a text file which contains SQL query into a single column in SAS Dataset. Could you please help.

For Example, I have a emp.txt file and which contains below query.
Select * from emp where sal > 5000 and deptno=30;

Above one is simple query, but I have big queries too. Thank you.

Re: How to read text file data into Single column in SAS

PaigeMiller — Thu, 21 Sep 2023 13:41:50 GMT

SAS data sets do not usually contain SQL code, they usually contain data. Are you sure that is the requirement? SAS data sets would be a poor choice for a place to store code. Could it be that the requirement is to read SQL code into a SAS program?

Re: How to read text file data into Single column in SAS

Banu — Thu, 21 Sep 2023 13:45:35 GMT

This is part of one of the automation. Users will give me one query, and I will need to break that query into all possible queries using where clause column /conditions to verify which query throwing 0 records.

Ex:
Query1: select * from emp where sal>5000;
Query2: select * from emp where sal>5000 and deptno=30;
Once I make the diff queries based on the input query, will submit queries to find whether all queries populating data or not.

As part of this, first I want to read the query into a single column in SAS Dataset.

Re: How to read text file data into Single column in SAS

Tom — Thu, 21 Sep 2023 13:48:33 GMT

@Banu wrote:
This is part of one of the automation. Users will give me one query, and I will need to break that query into all possible queries using where clause column /conditions to verify which query throwing 0 records.

Ex:
Query1: select * from emp where sal>5000;
Query2: select * from emp where sal>5000 and deptno=30;
Once I make the diff queries based on the input query, will submit queries to find whether all queries populating data or not.

As part of this, first I want to read the query into a single column in SAS Dataset.

Reading text from a file is trivial.

Making sense of it as SQL syntax is difficult.

One restriction is that character variables can only store 32K bytes. So your will have a limit on how large a block of code you can put into a single variable.

data want;
  infile 'codefile' truncover ;
  input codeline $32767.;
run;