BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
laiguanyu001
Fluorite | Level 6

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

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
Fluorite | Level 6
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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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
Fluorite | Level 6
Hey thanks! I think the simple format = 'DATE9.' actually works. I did something wrong when specifying other where conditions.
koyelghosh
Lapis Lazuli | Level 10
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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1935 views
  • 1 like
  • 3 in conversation