Proc sql select format

[laiguanyu001]

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. 

[Kurt_Bremser · Accepted solution]

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.

[laiguanyu001]

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!

[koyelghosh]

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

[Kurt_Bremser]

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

[koyelghosh]

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

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

[koyelghosh]

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.

[laiguanyu001]

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

[koyelghosh]

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.