sql和sas以及java_【转载】PROC SQL中常用的10个SAS函数

INTRODUCTION

Structured Query Language (SQL) is a universal computer language for all relational database management systems. PROC SQL is the implementation of the SQL syntax in SAS. It first appeared in SAS 6.0, and since then has been widely used for SAS users. PROC SQL greatly increases SAS’s flexibility in handling data, especially for multiple-table joining and database access. There are a number of comparisons between the DATA Step and the SQL procedure in SAS. A majority of SAS functions can be directly used in the SQL procedure. And the PROC procedure also enjoys a few unique functions. In this paper, we select the 10 SAS functions, and show their usage in the SQL procedure. For demonstration purpose, we simulate a Social Security Number (SSN) dataset with two entries from different sources. Each entry of the 1000 records should be identical but some values are missing.

*****(0) Simulate two datasets for demonstration********************;*****(0.1) Simulate a dataset for two SSN entries*******************;

data ssn_data;

do i=1 to 1000;

ssn1=ceil((ranuni(1234)*1E9));

ssn2=ssn1;if ssn1 le ceil((ranuni(1000)*1E9))then call missing(ssn1);if ssn2 le ceil((rannor(2000)*1E9))then call missing(ssn2);

drop i;

output;

end;

format ssn1 ssn2 ssn11.;

run;

We also simulate a patient-visiting dataset with three patient IDs. Every patient receives three different treatments at each visit. The effects of the treatments (1 means effective; 0 means not effective) and the cost for each visit are recorded. Other than the two simulated datasets, two datasets shipped with SAS, SASHELP.CLASS and SASHELP.CARS, are also used in the paper.

*****(0.2) Simulate a dataset for hospital visits ******************;

data hospital_data;

input id visit treat1 treat2 treat3 cost;

format cost dollar8.2;

cards;1 1 0 0 0 520

1 2 1 0 0 320

1 3 0 1 0 650

2 1 1 0 0 560

2 2 1 0 0 360

3 1 1 0 0 500

3 2 0 0 1 350;

run;

TOP 10 FUNCTIONS FOR THE SQL PROCEDURE IN SAS

1. The MONOTONIC function

The MONOTONIC function is quite similar to the internal variable _N_ in DATA Step. We can use it to select the records according to their row number. For example, we choose the SSNs from the 501th line to the 888th line in the SSN dataset.

****(1) MONOTONIC: specify row numbers******************************;

proc sql noprint;

create table ssn_data1 as

select*

fromssn_data

where monotonic() between501 and 800;

quit;

2. The COUNT, N and NMISS functions

These counting functions are especially useful in data cleaning. By using them, the detailed missing status is shown in only one output table. For the SSN dataset, we can display the total numbers of the missing and non-missing values for each SSN entry.

****(2) COUNT/N/NMISS: find total and missing values****************;

proc sql;

select count(*) as n 'Total number of the observations',

count(ssn1) as m_ssn1'Number of the non-missing values for ssn1',

nmiss(ssn1) as nm_ssn1'Number of the missing values for ssn1',

n(ssn2) as m_ssn1'Number of the non-missing values for ssn2',

nmiss(ssn2) as nm_ssn2'Number of the missing values for ssn2'

fromssn_data;

quit;

3. The COALESCE function

The COALESCE function does the magic to combine multiple rows into a single one with any non-missing value. In this example, there are two rows of SSNs, and supposedly they should be identical each other. However, some of them are missing due to input errors or other reason. The COALESCE function in the SQL statement below checks the value of the two rows and returns the first non-missing value, which maximizes the SSN information.

****(3) COALESCE: combine values among columns**********************;

proc sql;

select monotonic() as obs, coalesce(ssn1, ssn2) as ssn format=ssn11.fromssn_data;

quit;

4. The MISSING function

The MISSING function returns a Boolean value for a variable (0 when non-missing; 1 when missing). In the example below, the missing status of the values in the SSN dataset is displayed row by row.

****(4) MISSING: return Boolean for missing value*******************;

proc sql;

select monotonic() as obs,

(

case sum(missing(ssn1), missing(ssn2))

when 0 then'No missing'when1 then 'One missing value'

else'Both missing values'end

) as status'Missing status'

fromssn_data;

quit;

5. The SPEDIS and SOUNDEX functions

The two functions can fulfill fuzzy matching. For example, if we want to examine the first entry of the SSN dataset to see if there is any possible duplicate, we can use the SPEDIS function in the SQL statement to look up any pair of the records. Here we set the argument to be 25 in order to detect any singlet.

****(5)SPEDIS/SOUNDEX: fuzz matching*********************************;****(5.1)SPEDIS: find spelling mistakes******************************;

proc sql;

select a.ssn1 as x, monotonic(a.ssn1) as x_obs,

b.ssn1 as y, monotonic(b.ssn1) as y_obsfromssn_data as a, ssn_data as b

where(x gt y)and (spedis(put(x,z11.),put(y,z11.)) le 25);

quit;

For human names, we can check similarities by the SOUNDEX function to avoid duplicates. The SASHELP.CLASS has 19 names.Phonically, John and Jane look similar according to the SOUNDEX function.

****(5.2)SOUNDEX: find phonic similarity*****************************;

proc sql;

select a.name as name1, b.name as name2from sashelp.class as a, sashelp.classas b

where soundex(name1)=soundex(name2) and(name1 gt name2);

quit;

6. The RANUNI function

This function does simple random sampling like PROC SURVEYSELECT. We can specify the OUTOBS option at the beginning to choose the sample size.

****(6)RANUNI: simple random sampling********************************;

proc sql outobs=30;

select*

fromssn_data

order by ranuni(1234);

quit;

7. The MAX function

The MAX function returns the maximum value and sometimes simplifies column-wise aggregation. For the patient-visiting dataset, if we need to know if each treatment is effective for the patients, it may take some time to code the RETAIN statement and temporary variables at DATA Step, while the MAX function at PROC SQL is quite straightforward.

****(7)MAX: find the maximum value for each column******************;

proc sql;

select id, max(treat1) as effect1'Effect after Treatment 1',

max(treat2) as effect2'Effect after Treatment 2',

max(treat3) as effect3'Effect after Treatment 3'

fromhospital_data

group by id;

quit;

8. The IFC and IFN functions

The two functions play a role like the CASE-WHEN-END statements in typical SQL syntax, if the condition is about a binary selection. The IFC function deals with character variables, while the IFN function is for numbers. For the patient-visiting dataset, we can use the two functions together to find the total cost, the discounted cost (a 15% discount is applied if the total cost is greater than $1,000), and whether the first treatment is effective for each patient.

proc sql;

select id, ifc(max(treat1)=1,'Yes','No') as overall_effect

length=3 'Any effect after treatment 1',

sum(cost) as sum_cost format=dollar8.2 'Total cost',

ifn(calculated sum_cost ge1000,calculated sum_cost*0.85,calculated sum_cost*1) as discounted_cost

format=dollar8.2 'Total cost after discount if any'

fromhospital_data

group by id;

quit;

9. The UNIQUE function

This function is very convenient to show the number of the levels for every categorical variable.

proc sql;

select count(unique(make)) as u_make'Number of the car makers',

count(unique(origin)) as u_origin'Number of the car origins',

count(unique(type)) as u_type'Number of the car types'

fromsashelp.cars;

quit;

10. The PUT function

We can apply the PUT function with a user-defined format by PROC FORMAT in the WHERE statement to create filters. For the SASHELP.CARS dataset, this strategy is used to choose only the high or medium priced cars.

****(10)PUT: create an filter by user-defined format***************;

proc format;

value range40000-high='High' 26000-< 40000='Medium' other ='Low';

run;

proc sql;

select model,make,msrp,msrp as range'Price Range' format=range.fromsashelp.cars

where put(msrp, range.)in('High', 'Medium');

quit;

CONCLUSION

The combination of SAS’s powerful functions and the SOL procedure will benefit SAS users in data management and descriptive statistics.