{"id":11832,"date":"2018-03-27T06:20:12","date_gmt":"2018-03-27T06:20:12","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=11832"},"modified":"2021-05-09T13:16:41","modified_gmt":"2021-05-09T07:46:41","slug":"sas-sql","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sas-sql\/","title":{"rendered":"SAS SQL – A Complete PROC SQL SAS Guide"},"content":{"rendered":"

In this SAS SQL Tutorial, we will show you 5 different ways to manipulate and analyze your data using the\u00a0SAS SQL procedure and PROC SQL SAS.<\/p>\n

Moreover, we will see the comparisons on how to accomplish the same task with base SAS code are also made throughout the article with some SAS SQL example.<\/p>\n

So, let’s start with SAS SQL.<\/p>\n

SAS SQL – PROC SQL SAS<\/h2>\n

The procedure\u00a0PROC SQL\u00a0is used to process the SQL statements. This procedure can not only give back the result of an SQL query, it can also create SAS tables & variables<\/strong>.<\/p>\n

The syntax of PROC SQL SAS-<\/strong><\/p>\n

PROC SQL:<\/strong>\u00a0calls the SAS SQL procedure
\nSELECT:<\/strong>\u00a0specifies the column(s) (variables) to be selected
\nFROM:<\/strong>\u00a0specifies the table(s) (data sets) to be queried
\nWHERE:<\/strong>\u00a0subsets the data based on a condition
\nGROUP BY:<\/strong>\u00a0classifies the data into groups based on the specified column(s)
\nORDER BY:\u00a0<\/strong>sorts the resulting rows (observations) by the specified column(s)
\nQUIT:<\/strong>\u00a0ends the PROC SQL procedure<\/p>\n

 <\/p>\n

\"SAS<\/a>

SQL vs SAS<\/p><\/div>\n

 <\/p>\n

PROC SQL STATEMENTS<\/h2>\n

The statements used in SAS SQL for creating, updating and viewing datasets differ from the statements. Here is a summary of the differences. We will be looking at the important ones from this table.<\/p>\n

\"PROC<\/a>

PROC SQL STATEMENTS<\/p><\/div>\n

a. Display Data Set<\/h3>\n

The most basic usage of PROC SQL\u00a0is to display (or print) all variables (columns) and observations (rows) from a given dataset in the SAS Results window.<\/p>\n

Using the SASHELP.CLASS dataset with Base SAS code, you can see here how to print the entire dataset to the results window using the PRINT procedure:<\/p>\n

proc print data=sashelp.class;\r\nrun;<\/pre>\n
 <\/p>\n
results can be obtained using a SELECT statement. To display all columns in the Results window, an asterisk (*) is used following a SELECT to indicate that you would like to keep all variables (columns) in the output.<\/p>\n
A call to PROC SQL SAS is concluded with a semi-colon, followed by a QUIT statement and another semi-colon as shown here:<\/p>\n
proc sql;\r\n\u00a0select * from sashelp.class\r\n\u00a0;\r\nquit;<\/pre>\n
\"SAS<\/a>
Display Data Set in PROC SQL\u00a0SAS<\/p><\/div>\n
For datasets with a large number of variables, it may be preferable to only view a subset of those variables. With Proc Print, this would be accomplished with a VAR statement. Here, the VAR statement is used to print only the\u00a0Name<\/strong>\u00a0and\u00a0Age<\/strong>\u00a0variables from SASHELP.CLASS:<\/p>\n
proc print data=sashelp.class;\r\n\u00a0var\u00a0name\u00a0age;\r\nrun;<\/pre>\n
proc sql;\r\n\u00a0select\u00a0name,age\u00a0from sashelp.class;\r\nquit;<\/pre>\n
\"SAS<\/a>
Displaying Data Set in PROC SQL\u00a0SAS<\/p><\/div>\n
b. Creating Dataset from Existing Data<\/h3>\n
Similar to the Data Step in base SAS programming<\/strong>, PROC SQL\u00a0can also be used to create new datasets from existing data.<\/p>\n
To create a new dataset in the WORK library called class_new, which contains all the variables and observations from SASHELP.CLASS, the Base SAS data step is used along with a SET statement as follows:<\/p>\n
Data class_new;\r\n\u00a0Set sashelp.class;\r\nRun;<\/pre>\n
The equivalent output dataset is produced by using\u00a0CREATE TABLE\u00a0and\u00a0AS statements before the SELECT statement:\u00a0<\/strong><\/p>\n
proc sql;\r\n\u00a0create table\u00a0class_new\u00a0as\r\n\u00a0 select * from sashelp.class\r\n\u00a0 ;\r\nquit;<\/pre>\n
\"SAS<\/a>
Creating Dataset from Existing Data<\/p><\/div>\n
c. The WHERE Clause<\/h3>\n
SAS uses where clause to subset data, that is, to get only the values that we want and discard the rest of the values. Example, if we want only people whose gender is male, then we will specify it in a where clause.<\/p>\n
We have created a dataset called CLASSFIT_MALES which contains all variables from CLASSFIT but only those records where Sex is \u2018M\u2019 (i.e. only those records for males).:<\/p>\n
data classfit_males;\r\n\u00a0set sashelp.classfit;\r\n\u00a0 where sex = 'M';\r\nrun;\u00a0\r\nproc sql;\r\n\u00a0 select * from classfit_males where sex=\u2019M\u2019\r\n\u00a0 ;\r\nquit;\u00a0\u00a0\r\n<\/pre>\n
 <\/p>\n
\"SAS<\/a>
WHERE Clause – SAS SQL<\/p><\/div>\n
d.\u00a0Updating the Dataset<\/span><\/h3>\n
Using the CLASS dataset as an example, say that you would like to know what the height is for each person in both inches and centimeters.<\/p>\n
Using a single select statement, the name variable is kept, the current height variable can be renamed to height_inches, and a new height variable called height_cm can be created by multiplying the height in inches by 2.54 to convert to centimeters.<\/p>\n
proc sql;\r\n\u00a0create table class_heights as\r\n\u00a0select name, height as height_inches, (height*2.54) as height_cm from sashelp.class;\r\nquit;<\/pre>\n
\"SAS<\/a>
SAS SQL<\/p><\/div>\n
e. Deleting Rows from the Dataset<\/h3>\n
Just like we saw some of the above statements, the delete statement inside the PROC SQL SAS deletes rows of our choice.<\/p>\n
proc sql;\r\n\u00a0\u00a0 delete\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 from sashelp.cars\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 where salary>200\r\n;\r\nquit;<\/pre>\n
This was all on SAS SQL Tutorial. Hope you like our explanation.<\/p>\n
Conclusion<\/h2>\n
Hence, today we looked what is SAS SQL and how PROC SQL statements can be used instead of SAS statements to apply operations on our dataset. There is not much difference between the statements.<\/p>\n
We also saw how to view entire data, change it, view data of our interest, delete it and also change it temporarily in SAS SQL. Hope you all liked it. Stay tuned for more.
\nFurthermore, if you have any query, feel free to ask in a comment box.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this SAS SQL Tutorial, we will show you 5 different ways to manipulate and analyze your data using the\u00a0SAS SQL procedure and PROC SQL SAS. Moreover, we will see the comparisons on how...<\/p>\n","protected":false},"author":6,"featured_media":11848,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[59],"tags":[3136,3978,10113,12259,12260,12261,12262,15176,16099],"class_list":["post-11832","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sas","tag-creating-dataset-from-existing-data","tag-display-data-set","tag-proc-sql-sas","tag-sas-sql","tag-sas-sql-example","tag-sas-sql-statements","tag-sas-sql-tutorial","tag-updating-the-dataset","tag-where-clause"],"yoast_head":"\nSAS SQL - A Complete PROC SQL SAS Guide - DataFlair<\/title>\n<meta name=\"description\" content=\"What is SAS SQL- PROC SQL SAS, PROC SQL Statements,PROC SAS SQL can - Display data set,Creating Dataset from Existing Data,WHERE Clause,Deleting Rows in SQL\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sas-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SAS SQL - A Complete PROC SQL SAS Guide - DataFlair\" \/>\n<meta property=\"og:description\" content=\"What is SAS SQL- PROC SQL SAS, PROC SQL Statements,PROC SAS SQL can - Display data set,Creating Dataset from Existing Data,WHERE Clause,Deleting Rows in SQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sas-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-27T06:20:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-09T07:46:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/SAS-SQL-01.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SAS SQL - A Complete PROC SQL SAS Guide - DataFlair","description":"What is SAS SQL- PROC SQL SAS, PROC SQL Statements,PROC SAS SQL can - Display data set,Creating Dataset from Existing Data,WHERE Clause,Deleting Rows in SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sas-sql\/","og_locale":"en_US","og_type":"article","og_title":"SAS SQL - A Complete PROC SQL SAS Guide - DataFlair","og_description":"What is SAS SQL- PROC SQL SAS, PROC SQL Statements,PROC SAS SQL can - Display data set,Creating Dataset from Existing Data,WHERE Clause,Deleting Rows in SQL","og_url":"https:\/\/data-flair.training\/blogs\/sas-sql\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-27T06:20:12+00:00","article_modified_time":"2021-05-09T07:46:41+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/SAS-SQL-01.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SAS SQL – A Complete PROC SQL SAS Guide","datePublished":"2018-03-27T06:20:12+00:00","dateModified":"2021-05-09T07:46:41+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/"},"wordCount":818,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/SAS-SQL-01.jpg","keywords":["Creating Dataset from Existing Data","Display Data Set","PROC SQL SAS","SAS SQL","SAS SQL example","SAS SQL Statements","SAS SQL tutorial","Updating the Dataset","WHERE Clause"],"articleSection":["SAS Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sas-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/","url":"https:\/\/data-flair.training\/blogs\/sas-sql\/","name":"SAS SQL - A Complete PROC SQL SAS Guide - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/SAS-SQL-01.jpg","datePublished":"2018-03-27T06:20:12+00:00","dateModified":"2021-05-09T07:46:41+00:00","description":"What is SAS SQL- PROC SQL SAS, PROC SQL Statements,PROC SAS SQL can - Display data set,Creating Dataset from Existing Data,WHERE Clause,Deleting Rows in SQL","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sas-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/SAS-SQL-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/SAS-SQL-01.jpg","width":1200,"height":628,"caption":"What is SAS SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sas-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SAS Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sas\/"},{"@type":"ListItem","position":3,"name":"SAS SQL – A Complete PROC SQL SAS Guide"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11832","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=11832"}],"version-history":[{"count":6,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11832\/revisions"}],"predecessor-version":[{"id":93364,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11832\/revisions\/93364"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/11848"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=11832"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=11832"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=11832"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}