5 examples introducing a number of tips & tricks using PROC SQL to do other stuff than join - and some more...   It's not a discussion about advantages/disadvantages or trying to provide any best practices regarding using Proc SQL versa other proc’s - Just some inspiration 😉   You should be able to copy, paste and run the code. Link to documentation at the bottom for more information.   * 1) proc freq count *; * simple Proc freq etc *; Proc Freq data=sashelp.cars; Tables model; Run; proc sql number /* NOEXEC */ ; /* validate */ select count(*) as row_count , count(model) as model_count , count(distinct model) as model_unique , nmiss(Cylinders) 'Cylinders_missing_value' /* alternative syntax naming column */ , nmiss(origin) as Origin_missing_value /* nmiss works with both num and char */ from sashelp.cars; quit; The first example is a simple Data Quality assessment - See if a column is unique / contains duplicates or contains missing values   * 2) Proc means calculations *; * same function can sum more columns or more rows *; * columns / down (aggregate function)*; proc sql ; create table means_sql as select make, mean(MPG_Highway) as mean_MPG_Highway, avg(MPG_Highway) as avg_MPG_Highway from sashelp.cars group by make; quit; proc means data=sashelp.cars mean min max; by make; var MPG_Highway; output out=cars_proc; run; * colums / across (scalar function)*; proc sql; select MONOTONIC() AS ROW_NUM, make, model, mean(MPG_Highway,MPG_Highway) as MPG from sashelp.cars; quit; Can you spot the undocumented feature?   * 3) Duplicates ? *; * proc sort Duplicates - data contains 428 rows*; proc sql feedback /* expanding select * */; create table dist_model_sql425 as select distinct model from sashelp.cars; %put SQLOBS=&SQLOBS. SQLRC=&SQLRC. SQLOOPS=&SQLOOPS. SQLEXITCODE=&SQLEXITCODE.; create table dups_model_sql6 as select * /* look in the log to see the expanded "select *" */ from sashelp.cars group by model having count(model) > 1; quit; proc sort data=sashelp.cars out=dist_model_proc425 NODUPKEY /* delete one of the duplicates */ dupout=dups_model_proc3; * output keys that has duplicates *; by model; run; proc sort data=sashelp.cars(keep=make model drivetrain) out=dist_model_proc6 NOUNIQUEKEY /* rows with duplicates keys */ uniqueout=dups_model_proc422; * remove rows with duplicate keys (428-6); by model; run; * Find rows in one table not in the other *; proc sql; title 'sql6 (drivetrain rear,all)'; select * from dups_model_sql6; title 'proc3 (drivetrain all)'; select * from dups_model_proc3; title 'Rows from sql6 not in proc3 - (drivetrain=rear)'; select * from dups_model_sql6 except select * from dups_model_proc3; quit; title; Pay close attention to the number of OBS, and the output of each table   * 4) simple proc Rank *; * simple PROC RANK - rank = 1 = worst*; proc sql; create table rank_sql as select make,model, a.MPG_City , min(MPG_City) as min_MPG_City , avg(MPG_City) as avg_MPG_City format=3. , max(MPG_City) as max_MPG_City , (select count(distinct b.MPG_City) from sashelp.cars b where b.MPG_City <= a.MPG_City) as rank from sashelp.cars a order by rank desc; quit; proc rank data=sashelp.cars out=rank_proc ties=low /* descending */; var MPG_City; ranks rank; run; So, what if we wanted the best MPG to have number 1 ?   * 5) simple proc surveyselect *; proc sql outobs = 8; create table rand_sql as select * from sashelp.cars order by rand('Normal'); quit; proc surveyselect data=sashelp.cars out=rand_proc method=srs sampsize=8 noprint; run; Are you still using ranuni() ?   Link to Documentation (hover above link to see hint):   options fullstimer Proc Freq Proc sql NOEXEC Validate Proc sql number count(distinct X) Nmiss() – have a look at Cmiss also Proc means MONOTONIC  undocumented SAS function! SQLOBS, SQLRC, SQLOOPS, SQLEXITCODE feedback – expanded “select *” Proc sort NOUNIQUEKEY / UNIQUEOUT= Proc sql except Proc rank Proc surveyselect rand() function ... View more

SAS Juletip #9 this year is an introduction on ways to easily read and write multiple sources of data with filename wildcards, dynamic filenames, and keeping track of the source of the data.   Hopefully a few tips or recaps of forgotten features 😉   The examples are very simple, but hopefully, you get the general idea and can adjust them to your own needs. You should be able to copy, paste, and run the code in your favorite SAS Editor  and SAS Version ....   A1) generate two flat files, naming the files based on the data SAS PGM SAS log (partly) * Create flatfile by groups - Method 1 *; %macro makefile(gender); data _null_; * Only select some of the data *; set sashelp.class(where=(sex="&gender")); * create variable containing dynamic filename *; filename=cats('C:\temp\gender_',sex,'.csv'); * use the custom filename when creating the files *; file dynfile filevar=filename dlm=";"; put name age sex height weight; run; %mend; %makefile(M); %makefile(F); NOTE: The file DYNFILE is: Filename=C:\temp\gender_M.csv, NOTE: 10 records were written to the file DYNFILE. NOTE: There were 10 observations read from the data set SASHELP.CLASS. WHERE sex='M';   A2) Creating 2 flat files named by content, with no macro SAS PGM SAS Log (partly) * Method 2 *; * require that the data is sorted by groups *; proc sql; create view _temp_ as select * from sashelp.class order by sex; quit; data _null_; set work._temp_; * variable containing dynamic filename based on content *; filename=cats('C:\temp\gender_',sex,'.csv'); file dynfile filevar=filename dlm=";"; put name age sex height weight; run; NOTE: The file DYNFILE is: Filename=C:\temp\gender_F.csv,   NOTE: The file DYNFILE is: Filename=C:\temp\gender_M.csv,   NOTE: 9 records were written to the file DYNFILE.   NOTE: 10 records were written to the file DYNFILE.     B) Reading files with wildcard and tracking origin SAS PGM SAS log (partly) data all_genders; length _filename_ $40; infile "c:\temp\gender_*.csv" dlm=';' filename=_filename_; input name $ age sex $ height weight; filename=scan(_filename_,-2,'\.'); run; NOTE: The infile "c:\temp\gender_*.csv" is: Filename=c:\temp\gender_F.csv, File List=c:\temp\gender_*.csv   NOTE: The infile "c:\temp\gender_*.csv" is: Filename=c:\temp\gender_M.csv, File List=c:\temp\gender_*.csv   NOTE: 9 records were read from the infile "c:\temp\gender_*.csv". NOTE: 10 records were read from the infile "c:\temp\gender_*.csv".   NOTE: The data set WORK.ALL_GENDERS has 19 observations and 6 variables.   C) Which SAS Dataset did the data originate from, reading SAS data with wildcard SAS PGM SAS output (partly) * create simple data for example *; data work.gender_male work.gender_female; set sashelp.class; if sex='F' then output gender_female; else if sex='M' then output gender_male; run; * Read data and mark the origin *; data class; * read all data sets beginning with "gender_"*; set gender_: indsname=_source_; source_data=_source_; run; Obs  Name Sex Age Height  Weight  source_data 1 Alice F 13 56.5 84.0 WORK.GENDER_FEMALE 2 Barbara F 13 65.3 98.0 WORK.GENDER_FEMALE .. .... . .. ... ... ...... 18 Thomas M 11 57.5 85.0 WORK.GENDER_MALE 19 William M 15 66.5 112.0 WORK.GENDER_MALE   From the SAS Documentation filename - get the filename filevar - set the filename indsname - get the dataset name scan - with negative value / read from right cats - concatenate with no blanks Data Set Lists - using : as wildcard   "Very well done if you got all of those at home." - Richard Osman / Pointless ... View more

Hi all   When we Print to PDF in SAS VA 7.5 (on Windows Server 2012R2) it takes 1-2 minutes to get the PDF file (as download) in the browser (Chrome, Edge, IE - on Windows 10)    Can this be optimized somehow, users are impatient and are printing multiple times.   (Performance when using the report 'online' in the browser is fine) ... View more

A #SASJuletip advent calendar about a calendar 😉 You might already be using the INTCK functions to calculate various distances between different standard dates, but there are some limitation as it does not handle national bank holidays or perhaps even special company holidays. As always there are many ways to accomplice this, but this #SASjuletip will introduce an old and perhaps a little know/used feature. Imagine these dates. How many days are between the dates marked with yellow? Properly different answers depending on your country. And it can be calculated using SAS in many different ways, depending on what you want and your preferred method. Below is one way, using a simple example of introducing Custom Date Intervals used together with INTCK().   * Table with public bank holidays we will not count a workdays *; * http://www.nationalbanken.dk/da/Kontakt/aabningstider/Sider/Default.aspx *; data bankdays; attrib begin format=date. ; infile datalines dlm=':'; input begin :anydtdte18. beskrivelser $30.; datalines; 1. januar 2019 : Nytårsdag 18. april 2019 : Skærtorsdag 19. april 2019 : Langfredag 22. april 2019 : 2. påskedag 17. may 2019 : Store bededag 30. may 2019 : Kristi himmelfartsdag 31. may 2019 : Banklukkedag 5. june 2019 : Banklukkedag 10. june 2019 : 2. pinsedag 24. december 2019 : Banklukkedag 25. december 2019 : Juledag 26. december 2019 : 2. juledag 31. december 2019 : Banklukkedag 1. januar 2020 : Nytårsdag ; run; proc sort data=bankdays; by begin; run; * All dates, but not saturday/sundays *; data xdanish(keep=begin); format begin date9.; do begin = '20dec2019'd to '05jan2020'd; if weekday(begin) not in (1,7) then output; * not sat/sun *; end; run; proc sort data=xdanish; by begin; run; * remove bank holidays from date tabel *; data xdanish; merge xdanish(keep=begin) bankdays(keep=begin in=remove); by begin; if remove then delete; run; options intervalds=(xDanish=work.xdanish); * TEST count days *; data test; format start end date.; start = '20dec2019'd; end = '22dec2019'd; put (start end) (=); day =intck('day', start, end); put day=; wday=intck('weekday', start, end); put wday=; xday=intck('xDanish', start, end); put xday= /; output; start = '23dec2019'd; end = '27dec2019'd; put (start end) (=); day =intck('day', start, end); put day=; wday=intck('weekday', start, end); put wday=; xday=intck('xDanish', start, end); put xday= /; output; start = '20dec2019'd; end = '2jan2020'd; put (start end) (=); day =intck('day', start, end); put day=; wday=intck('weekday', start, end); put wday=; xday=intck('xDanish', start, end); put xday=; output; run; Do you agree with the counts? Or did your expect another result? How would you solve this?   More reading: About Date and Time Intervals INTCK function Date and Time Intervals INTERVALDS= System Option Paper: Sometimes One Needs an Option with Unusual Dates Intervals, Enhanced Dating for SAS® Programmers DATDIF Function Btw are you familiar with HOLIDAY Function (7 different) but limited regarding holidays NWKDOM Function ... View more

Environment:  Windows: X64_SR12R2 WIN 6.3.9600 Server SAS 9.04.01M4P110916 Microsoft SQL Server Management Studio 14.0.17254.0 SAS/ACCESS: ODBC other DBMS SAS Data Integration Studio: 4.902 Background: 2 SAS libname pointing to the same MS SQL Server, but 2 different databases   Problem: ineffective SQL performance   Story: SAS Data Integration Studio's SQL Join Transformation does not make perfect Queries because libnames are not 100% identical (same MS SQL server, two Databases)   Here are 3 scenarios Implicit join - one libname (default in DIS) Explicit join - one libanme  (in Join Properties/Pass Through=Yes) Join - two libnames - only implicit join (explicit not possible because of two libname)   Regarding scenario 3 - If I don't want to to use "User Written Code" in order to write a SQL Execute Query to enforce Explicit execution like below, that would otherwise work perfectly (would like to maintain auto-generated DIS code if possible) * one SQL server, two SQL Database, one SAS connection*; proc sql; connect using gnis_DDL; execute ( select [Booking].[ACCOUNT_TYPE] , T_ACCOUNT_TYPE.DESCRIPTION , count(*) as count from [DDL].[dbo].[Booking] left join [DW_HIST].dbo.T_ACCOUNT_TYPE on [Booking].ACCOUNT_TYPE = T_ACCOUNT_TYPE.id group by [Booking].[ACCOUNT_TYPE], T_ACCOUNT_TYPE.DESCRIPTION ) by gnis_DDL; DISCONNECT FROM gnis_DDL; quit; What are my options?   Is there a secret option in DIS I haven't found? Do I need to re-arrange the MS SQL server to have all databases merged into one database with several schemas? Lose all metadata trace and apply "User Written Code" SQL Execute? Would it be better to change from SAS/ACCESS ODBC to SAS/ACCESS for MS SQL? or something else....? Attached a sample log for extra information. ... View more