Stack Overflow
Stack Overflow

Questions tagged [proc-sql]

proc sql is a SAS procedure used to submit SQL statements to the SAS compiler. For Oracle Pro*C, please use [oracle-pro-c].

proc sql; is a SAS procedure used to submit SQL statements to the SAS compiler. It is optionally terminated by a quit; statement. An example of typical syntax would be:

proc sql;
create table z as
  select * 
  from x
  left join y
  on x.id=y.id
  order by 1,2,3;
quit;

A more 'SAS specific' example would be the direct creation of macro variables, such as:

proc sql noprint; 
select someVariable into: MyMacroArray separated by ' ' 
  from work.Input where varCondition='True';

For further details on syntax, click here (v9.4) or here (v9.3)

794 questions
0
votes
1 answer

Count child rows into parent row in hierarchical column

I'm working in SAS with an oracle table with a hierarchical codelist column, a COUNT column and a hierarchy indicator column and need to update COUNT column for the parent nodes based on the count of the childs under the node. ID level code …
Martin
  • 27
  • 1
  • 7
0
votes
3 answers

Aliases and Group By Statements in SAS Proc SQL

I am working with proc SQL in SAS and one of my proc sql queries is behaving very oddly: I have a large data set (about 1 Million rows), which looks something like this: apple_key profit price cost months date golden_d 0.03…
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
1 answer

merging tables by using PROC SQL where in statement in SAS

everyone. I'm currently using SAS. Here's my situation. I have a table called 'a', which looks like: id_c id_t 5 3 2 9 15 1 65 43 ... ... This,a, table has only two different ids from other tables. These 5&3, 2&9,... are…
Todd
  • 399
  • 3
  • 18
0
votes
1 answer

SAS: Spliting data groups into its own rows

I'm new to SAS and found myself having to use it for my new job. So this is a basic question. I have a table which looks like this: COMPANY | Employee --------|---------- COMP1 | @,@,@,@ COMP2 | @ COMP3 | @,@ ... Every @ is an email adress -…
JackTheSkipper
  • 3
  • 1
0
votes
1 answer

Save maximum column value to global macro variable in SAS

I am working in SAS Enterprise Guide and want to save the maximum value of a column into a global macro variable. I already know how to save the maximum value of a column into a regular macro variable: proc sql; SELECT max(column) INTO:…
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
1 answer

Delete table on join

In T-SQL I used to be able to do the following: delete t1 from table1 t1 join table2 t2 on t1.rowid = t2.rowid and t1.value <> t2.value I'd like to be able to do the same in SAS. taking the code above and wrapping in proc sql; and quit; throws a…
Ben
  • 485
  • 9
  • 19
0
votes
2 answers

Create a table with all permutations of some column values in SAS

I am working in SAS Enterprise guide and want to create a table that contains all possible permutations of some columns. Here is an example: Lets say I have three columns apple pear plum 0 …
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
4 answers

SAS/SQL - find 3 or more occurences within a given timeframe

I have hundreds of thousands of rows of data (like the example below) which consists of account number and the dates of calls that were placed. I need to find all accounts where there were 3 or more calls placed within a 7 day period. So, for the…
UncleCross
  • 33
  • 3
0
votes
3 answers

Select many columns and other non-continuous columns to find duplicate?

I have a dataset with many columns like this: ID Indicator Name C1 C2 C3....C90 A 0001 Black 0 1 1.....0 B 0001 Blue 1 0 0.....1 B 0002 Blue 1 0 0.....1 Some of the IDs are duplicates because the indicator is…
PinkyL
  • 341
  • 1
  • 8
  • 19
0
votes
2 answers

How to create an output row if a proc sql “group by” group has no no observations

I am working in SAS Enterprise guide and am running a proc sql query as follows: proc sql; CREATE TABLE average_apples AS SELECT farm, size, type, mean(apples) as average_apples FROM input_table GROUP BY farm, size, type …
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
1 answer

Comparing two date variables in SAS in a proc sql WHERE clause

I am using SAS Enterprise guide and want to compare two date variables: My code looks as follows: proc sql; CREATE TABLE observations_last_month AS SELECT del_flag_1, gross_exposure_fx, reporting_date format=date7.,…
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
0 answers

Substring in inner join with contains sas

I'm trying to run the following code PROC SQL; CREATE TABLE _check AS SELECT DISTINCT * FROM table1 as a INNER JOIN table2 as b ON ON UPCASE(b.Cname) contains UPCASE(SUBSTRING(CNAMN, 1, 4)) ; quit; but I get the…
bettingdelo
  • 25
  • 6
0
votes
2 answers

Horizontally combine three SAS datasets with renaming of columns

I am working in SAS Enterprise guide and want to combine three separate data sets into one data set. All data sets have one column with the same length, data type and column header: My data looks as follows (three tables, 1 column each): data1 …
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
2 answers

ERROR: Expression using IN has components that are of different data types

I am using the below query in SAS Enterprise Guide to find the count for different offer_ids customers for different dates : PROC SQL; CREATE TABLE test1 as select offer_id, (Count(DISTINCT (case when date between '2016-11-13'…
Anupam Das
  • 11
  • 1
  • 4
0
votes
1 answer

SAS - Using variable in table name

Relatively new to SAS but I have come across an unusual issue. I use some proc sql statements to dynamically create a variable based on the latest month. The tables I want to retrieve from have a month name in the middle of them and I want to loop…
mugenheimer
  • 5
  • 1
  • 3
1 2 3
…
52 53