topic Re: Proc sql select format in SAS Programming

Proc sql select format

laiguanyu001 — Fri, 28 Jun 2019 14:44:30 GMT

Hi,

I want to use proc sql to select data where format is DATE9. So I tried

select * from sashelp.vcolumn

where format = 'DATE9.';

I also tried where format = DATE9. without quotation marks.

Both commands select 0 rows from the data.

Please let me know what I did wrong.

Re: Proc sql select format

Kurt_Bremser — Fri, 28 Jun 2019 14:57:01 GMT

Maxim 3 (Know Your Data) is also valid for the dictionary tables.

Run the same SQL for a specific column where you know that it has DATE9. as format, and look at the value in the format column.

Hint: In SQL, don't use the SASHELP views, use the underlying tables, in this case dictionary.columns.

Re: Proc sql select format

koyelghosh — Fri, 28 Jun 2019 14:58:44 GMT

That is a little strange because when I did the following it worked ..

PROC SQL;
	SELECT * FROM sashelp.vcolumn WHERE format LIKE 'DATE9.';
RUN;

Re: Proc sql select format

koyelghosh — Fri, 28 Jun 2019 15:02:03 GMT

Ok may be because the format of the "format" column (:-)) is 49 chracters wide and if you adjust for it as below it selects

PROC SQL;
	SELECT * FROM sashelp.vcolumn WHERE TRIM(format) = 'DATE9.';
QUIT;

Did it work? Please let me know.

Re: Proc sql select format

laiguanyu001 — Fri, 28 Jun 2019 15:08:00 GMT

Hey thanks! I think the simple format = 'DATE9.' actually works. I did something wrong when specifying other where conditions.

Re: Proc sql select format

laiguanyu001 — Fri, 28 Jun 2019 15:09:14 GMT

Hey thanks! format = 'DATE9.' actually works. I had a different mistake when specifying other where conditions. Thanks for the hint tho I think this is great help!

Re: Proc sql select format

koyelghosh — Fri, 28 Jun 2019 15:09:22 GMT

Great. I actually did not test that. I took it for granted that it is not working. Should have tested. Glad that it worked out.

Re: Proc sql select format

koyelghosh — Fri, 28 Jun 2019 15:12:00 GMT

@Kurt_Bremser .. Very nice compilation --> https://communities.sas.com/t5/SAS-Communities-Library/Maxims-of-Maximally-Efficient-SAS-Programmers/ta-p/352068

Re: Proc sql select format

Kurt_Bremser — Tue, 02 Jul 2019 12:51:35 GMT

And a SAS GF paper now: https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3062-2019.pdf