BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi All,

Is it possible to use a variable containing a string value for specifying the dataset for a PROC segment.

I have the code snippet as below..

-----------------------------------------------------------------------------------------------------
data _NULL_;
set files;

PROC SQL DATA = file;
ODS CSV FILE = outputFileNameCSV;

select * from dictionary.tables where memname=datasetName;

run;

ODS CSV CLOSE;

-----------------------------------------------------------------------------------------------------
I have a DataSet called files in the default SAS work library work. It is created by the code preceeding the above code snippet. The DataSet contains the Data Columns

file | datasetName | outputFileNameCSV | and others not relevant in this discussion..

A sample observation(Row) in the Data Set is

C:\DAG\it00samppp.sas7bdat | it00samppp | C:\DAG\CSViles\it00samppp.csv

What I want to do is have the PROC SQL use the value in the column 'file(C:\DAG\SAS\Samples\it00samppp.sas7bdat), and the select statement to use the value in column 'datasetName'(it00samppp) and the ODS to use the output file name in column 'outputFileNameCSV' (C:\DAG\SAS\Tests\CSV Files\it00samppp.csv)

I am not able to figure out the exact syntax of how to do this.

SAS environment complains if I try to specify a variable name after DATA in

PROC SQL DATA = file;

Any ideas how to achieve this?

Thanks & Regards,
Neel
10 REPLIES 10
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Using PROC SQL or a DATA step, you can generate SAS macro variables which are to be used for string substitution in your SAS program. For a PROC SQL approach, explore using the INTO operator within the SELECT stmt or with the DATA step, explore using the CALL SYMPUT statement (SYMPUT is a SAS function) to generate the SAS macro variable.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Thanks Scott for your reply.

I didn't quite understand the suggestions..I will ry to read up the documentation little bit and see if I can get..

Meanwhile I tried a simple SAS program which has a PROC SQL which references a
data set directly by specifying its location...

--------------------------------------------------------------------------------
ODS CSV FILE = 'C:\DAG\SAS\Tests\CSV Files\TestDirRef2.csv';

proc sql DATA = 'C:\DAG\SAS\Tests\state_extract';

select * from dictionary.tables where memname='STATE_EXTRACT';
run;

ODS CSV CLOSE;
-------------------------------------------------------------------------------------------

I am getting the following error..

5 proc sql DATA = 'C:\DAG\SAS\Tests\state_extract';
----
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, BUFFERSIZE, CHECK, CODEGEN, CONSTDATETIME, DOUBLE, DQUOTE, ERRORSTOP, EXEC, EXITCODE, FEEDBACK, FLOW, INOBS, IPASSTHRU, LOOPS, NOCHECK, ODEGEN, NOCONSTDATETIME, NODOUBLE, NOERRORSTOP, NOEXEC, NOFEEDBACK, NOFLOW, NOIPASSTHRU, NONUMBER, NOPRINT, NOPROMPT, NOREMERGE, NOSORTMSG, NOSTIMER, NOTHREADS, NUMBER, OUTOBS, PRINT, PROMPT, REDUCEPUT, REDUCEPUTOBS, REDUCEPUTVALUES, REMERGE, SORTMSG, SORTSEQ, STIMER, THREADS, UNDO_POLICY.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

Any ideas??

Similar program with libname and data step works

Here is the working program

-----------------------------------------------------------------------------------------------------
libname sasdata2 'C:\DAG\SAS\Tests\';

data work;
set sasdata2.STATE_EXTRACT;
run;

ODS CSV FILE = 'C:\DAG\SAS\Tests\CSV Files\Test1.csv';

proc sql;
select * from dictionary.tables where memname='STATE_EXTRACT';
run;

ODS CSV CLOSE;

------------------------------------------------------------------------------------------------------
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
The presumption here is that you have a SAS dataset you want to read and build macro variables. Definitely ODS CSV is not for this purpose. If necessary, explore using PROC IMPORT to get your external data into a SAS dataset location. Also, your PROC SQL code must reference a libref in the DATA= keyword, requiring a preceding LIBNAME statement to declare the location of your SAS database reference.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
The ODS CSV statement is to output data into a csv file.

Basically, I want to extract the metadata for a SAS dataset whose location is in another dataset.and the the metadata needs to go in csv file.

So typically I will have a dataset containing location of all SAS datasets at a particular location on filesystem

The dataset will be something like -- say the name of DataSet is MYDATA

MYDATA has a column called file which has the location of datasets

example

file
--------------------------
C:\DAG\SAS\Test1\file1.sas7bdat
C:\DAG\SAS\Test2\file2.sas7bdat
C:\DAG\SAS\Folder1\sasfile3.sas7bdat
C:\DAG\SAS\Test1\file4.sas7bdat
C:\DAG\SAS\Test1\file5.sas7bdat

------------------------------------------------

For each of the above files I want to get the metadata exported to a csv file.
I don't know how to specify the string in this data set as the library referece to Proc Sql procedure..
Hope I've made my use case clear..

Thanks,
Neelam
DanielSantos
Barite | Level 11
Hmmm... so much ways of doing this.

Macro code will do this easily, mainly allowing you to create a loop that will process each row from the location dataset, fill some auxiliary macro variables, and extract the metadata.

But, macroless solutions are possible also.
Say for example, you could cycle through the location dataset and issue (through the execute routine) a sql dictionary tables/proc contents (I prefer the last) for each row, and concatenate the result to some dataset. Then you just have to proc export/ODS-proc print the result dataset to a CSV file.

* cycle and extract table metadata;
data _null_;
set file;
call execute('proc contents data = '''!! DATASETNAME !! ''''!!
' out = _CONTENTS noprint; run;'!!
'proc append base = CONTENTS data = _CONTENTS; run;');
run;

* output result to csv;
ods csv file= 'E:\Temp\CONTENTS.csv';
proc print data = CONTENTS noobs;
run;
ods trace on;
ods csv close;

Now, if you're going to run this piece of code more than one time on the same session, be sure to delete first the CONTENTS dataset.

Check the online documentation for the EXECUTE routine:
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a000543697.htm

And read some more about the Output Delivery System here:
http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/a002291014.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
deleted_user
Not applicable
Thanks Daniel for all your suggestions!. I will check if I can get it working with macro way..One more thing I wanted to convey is, I will have to extract metadata for each dataset in seperate csv file and not just one. The csv file name is also in the same dataset(as column OutputCSVFileName) as the name of the dataset is.

But before that,I have a sample program to extract metadata for a given dataset and I want to test it as a regular SAS program before coverting that to macro..

I have working version which looks like below..
------------------------------------------------------------------------------------
libname sasdata2 'C:\DAG\SAS\Tests\';

data work;
set sasdata2.STATE_EXTRACT;
run;

ODS CSV FILE = 'C:\DAG\SAS\Tests\CSV Files\Test1.csv';

proc sql;
select * from dictionary.tables where memname='STATE_EXTRACT';
run;

ODS CSV CLOSE;
-----------------------------------------------------------------------------------------------

But when I convert the above program to something like

ODS CSV FILE = 'C:\DAG\SAS\Tests\CSV Files\TestDirRef2.csv';

proc sql DATA = 'C:\DAG\SAS\Tests\state_extract';

select * from dictionary.tables where memname='STATE_EXTRACT';

run;

ODS CSV CLOSE;

-------------------------------------------------------------------------------------------------
I get error
for
proc sql DATA = 'C:\DAG\SAS\Tests\state_extract'; (compalianing abt some syntax error)

I want to try the above version because if this works then next step I can replace the
'C:\DAG\SAS\Tests\state_extract'; in the above line with a variable to point to the dataset name

Thanks & Regards,
Neel
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Review the use and syntax of PROC SQL - there is no DATA= parameter on the PROC statement. The appropriate syntax depends on whether you want to CREATE TABLE or use the selection results with INTO : for passing the results to the SAS macro processor or for other type of use.

Suggest reviewing the SAS DOC on PROC SQL and also consider using the SEARCH facility at SAS support website http://support.sas.com/ for SAS-hosted product manuals and also supplemental technical / conference reference information.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
I will check the documentation.

Sorry, I am just beginning to learn SAS. Its looks very different from other programming langauges that I'm used too like JAVA,C++,C..

I am posting the rest of the follow up questions on this thread on to a new thread..
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Frankly, you gain marginal benefit by starting a new post -- the information is contained here and suggestions have been provided. To start, your PROC SQL syntax is incorrect, meaning that you would benefit from the SAS MACRO Language documentation and also possibly from searching the SAS support website for related technical and conference "how to" or "introduction to" papers.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Thanks Scott.
I totally agree, I need to check the documentation..
I was rushing because I had to implement something urgent..
Anyways, will look through the documentation.

Thanks again for all your valuable feedback!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1176 views
  • 0 likes
  • 3 in conversation