Category: SAS SQL

SAS: Proc SQL Format

  • get variable with the required format from other table with sql join and format in one statement.
  • the example shows the new table contains the new character variable newvar1 with $1. format and the new date variable newvar2 with ddmmyy10. format
 proc sql;
create table new as
select a.* , b.var1 as newvar1 format=$1. , datepart(b.var2) as newvar2 format=ddmmyy10. 
from old1 as a 
left join lib.old2 as b
on a.var = b.var;
quit;

SAS Dictionary Members and Tables

Get a full list of the dictionary tables in the working session.

PROC SQL;
SELECT *
FROM dictionary.members;
QUIT;

There are following fields in the dictionary.members.

  • libname
  • memname
  • memtype
  • dbms_memtype
  • engine
  • index
  • path

Get number of numeric variables in a table.

Use nvar to get number of variables; use num_numeric to get numeric variables; use num_character to get character variables.

proc sql ;
select num_numeric into :num_vars
from dictionary.tables
where libname=”WORK” and memname=”TABLE1″;
quit;

Note:

  • Capitalize the input for libname and memname
  • Put the input for libname and memname in “”

Layout of dictionary.tables

Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat Label
26 attr Char 3 Data Set Attributes
35 audit Char 3 Audit Trail Active?
37 audit_admin Char 3 Audit Admin Image?
36 audit_before Char 3 Audit Before Image?
39 audit_data Char 3 Audit Data Image?
38 audit_error Char 3 Audit Error Image?
19 bufsize Num 8 Bufsize
13 compress Char 8 Compression Routine
7 crdate Num 8 DATETIME. DATETIME. Date Created
28 datarep Char 32 Data Representation
33 datarepname Char 170 Data Representation Name
4 dbms_memtype Char 32 DBMS Member Type
20 delobs Num 8 Number of Deleted Observations
34 encoding Char 256 Data Encoding
14 encrypt Char 8 Encryption
16 filesize Num 8 Size of File
25 gen Num 8 Generation number
27 indxtype Char 9 Type of Indexes
1 libname Char 8 Library Name
24 maxgen Num 8 Maximum number of generations
23 maxlabel Num 8 Longest label
22 maxvar Num 8 Longest variable name
5 memlabel Char 256 Data Set Label
2 memname Char 32 Member Name
3 memtype Char 8 Member Type
8 modate Num 8 DATETIME. DATETIME. Date Modified
21 nlobs Num 8 Number of Logical Observations
9 nobs Num 8 Number of Physical Observations
15 npage Num 8 Number of Pages
40 num_character Num 8 Number of Character Variables
41 num_numeric Num 8 Number of Numeric Variables
11 nvar Num 8 Number of Variables
10 obslen Num 8 Observation Length
17 pcompress Num 8 Percent Compression
12 protect Char 3 Type of Password Protection
32 reqvector Char 24 $HEX48. $HEX48. Requirements Vector
18 reuse Char 3 Reuse Space
31 sortchar Char 8 Charset Sorted By
29 sortname Char 8 Name of Collating Sequence
30 sorttype Char 4 Sorting Type
6 typemem Char 8 Data Set Type

SAS: Join or Merge Tables

Note: Be careful to use merge if the the byvar are not unique in both tables, especially when the vars are numeric and can be aggregated .

Compare of SQL method and merge method

Inner Join

SELECT var
FROM table1 as a
INNER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a and b;

Left Join

SELECT var
FROM table1 as a
LEFT JOIN as b
ON a.var=b.var; 

merge table1 (in=a) table2;
by var;
if a;

Left Join exclude Inner Join

SELECT var
FROM table1 as a
LEFT JOIN table as b
ON a.var=b.var;
WHERE b.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if a and not b;

Right Join

SELECT var
FROM table1 as a
RIGHT JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if b;

Right Join exclude Inner Join

SELECT var
FROM table1 as a
RIGHT JOIN table as b
ON a.var=b.var

WHERE a.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if b and not a;

Full Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a or b;

or

merge table1 (in = a) table2 (in=b);
by var;

Full Join Inner Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);

by var;
if a ne b; /* only work on 2 table merge*/

Proc sql to get fiscal year using put function

Dataset only has calendar year in numeric format.  The following sql statement calculates the fiscal start year and fiscal end year based on the calendar month and year.Case statement requires the results of the ‘when’ clause is the same as the ‘then’ clause, therefor the put function can’t be used to convert bud_year to a character value.
proc sql;
create table step1 as
select A.*, B.proposed_amount, B.awarded_amount, B.period, B.start_dt as bud_start_dt, B.end_dt as bud_end_dt, B.bud_month, B.bud_year,
case
when  B.bud_month>=5 then B.bud_year
when  B.bud_month<5 then B.bud_year-1
else .
end as bud_fiscalyr_start,
case
when  B.bud_month>=5 then B.bud_year+1
when  B.bud_month<5 then B.bud_year
else .
end as bud_fiscalyr_end
from projectlist as A
left join project_budget as B
on B.project_id = A.project_id
;
quit;

step1

bud_month     bud_year      bud_fiscalyr_start        bud_fiscalyr_end

4                          2014               2013                                     2014

5                           2014               2014                                    2015
proc sql;
create table step2 (drop=bud_fiscalyr_start bud_fiscalyr_end) as
select *,
case
when put(bud_fiscalyr_start,4.)=’   .’ then ”
when put(bud_fiscalyr_start,4.) ne ‘   .’ then put(bud_fiscalyr_start,4.)||”-“||put(bud_fiscalyr_end,4.)
end as bud_fiscal_year
from step1
;
quit;

step2

bud_month     bud_year      bud_fiscal_year

4                          2014               2013-2014

5                           2014               2014-2015

.                           .

 

SAS SQL: Merge dataset with multiple conditions

Assume all the macro variables are defined. There are two lists A and B. A is list of year n students and B is a list student id for year n+1 students in the same program.  Step 1 is to create table that includes all the students with study level 1 or 2 in A but not in B (1 year later).  This step identifies the student population of study level 1 and 2 who are lost to the program one year later. Notice A and B are used as table aliases to simplify the statement. Also the syntax for left join needs to be followed by  the on statement to specify the column field to join the two table.

proc sql;
create table nov&yr.not as
select A.* from nov&yr. as A
left join nov&nextyr.id as B
on A.sisid = B.id
where B.id is null and A.std_lvl in (’01’,’02’)
;
quit;

Second step is to identify the students who changed their major to Psychology from results of step 1.  Notice I omit As when I assign the aliases.
Proc sql;
create table nov&yr.chg as
select A.*, B.sisall, B.newm1 from nov&yr.not A, nov&nextyr.all B
where A.sisid = B.sisall
and A.major1 ne B.newm1
and B.newm1 eq ‘PSYC’
;
quit;