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

Cell by cell average of four tables in SAS

I have three tables which look look as follows: A B C A B C A B C D 1 2 3 D 2 3 4 D 3 4 5 E 4 5 6 E 5 6 7 E 6 7 8 F 7 …
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
1 answer

SAS: Is it possible to have SAS check entries across different rows?

The data I have looks like the picture below. I have the date, the customer name, and the different categories of fruit that they bought. I want to create a new column that count how many "double sold" occurred. For example, first interaction with…
Joe Chan
  • 133
  • 3
  • 11
0
votes
2 answers

Print a CSV file from SAS using libname

I am trying to export data from SAS into a CSV file from a table I created using PROC SQL. (test_data was created using a PROC SQL Select statement) I tried the following code: LIBNAME libout "C:\Users\Outbox"; proc export data=test_data dbms=csv…
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
1 answer

Insert date into date9

Within my PROC SQL snipped I am trying to compare a column of datatype date9. to the date 31.12.2015'. I tried: test_date = '31DEC2015' This returns me the following error: ERROR: Expression using equals (=) has components that are of different…
Martin Reindl
  • 989
  • 2
  • 15
  • 33
0
votes
1 answer

Finding the overlapping locations for a 5 mile and 10 mile radius for a list of location data with latittude and longitude

I have a 10,000 observation dataset with a list of location information looking like this: ADDRESS | CITY | STATE | ZIP |LATITUDE |LONGITUDE 1189 Beall Ave | Wooster | OH | 44691 | 40.8110501 …
V_N
  • 23
  • 4
0
votes
1 answer

SAS Rolling conditional statement (similar to excel)

I have a dataset and I would like to create a rolling conditional statement row by row (not sure what the exact term is called in SAS). I know how to do it in Excel but not sure on how it can be executed on SAS. The following is the dataset and what…
Molia
  • 311
  • 2
  • 17
0
votes
2 answers

Convert variable types from character to numeric with uncertain length in SAS

When I use PROC SQL statement in SAS, sometimes I need to convert variable from character to numeric or vice versa. I normally use the following two queries: INPUT(A.KEY_ID, 8.) = B.KEY_ID OR A.KEY_ID = PUT(B.KEY_ID, 8.) My question is, if the…
mumu.W
  • 53
  • 5
  • 11
0
votes
1 answer

SAS: What are the difference between "data files" and "raw data files"?

So, according to SAS books, you do: PROC IMPORT DATAFILE="filename" | TABLE="tablename" OUT=SAS-data-set ; for data files, and you use INFILE to read raw data files... So what exactly is "raw data files"? Are CSV files…
Jacky Chen
  • 183
  • 3
  • 6
0
votes
0 answers

SAS code Proc rank with groups option to SQL conversion

I am new to SAS and struggling to convert the below piece of code to SQL. proc rank data = input (where = (limit_assort = 1)) out = assort_rank groups=3; var assort; ranks assort_rank; run; I tried in sql by using the below one but not…
Samah
  • 1
  • 1
  • 2
0
votes
1 answer

Execute Proc SQL Statement inside a Do While or Do Until Statement

I have a sql database that updates anywhere from 4:00 am to 7:00 am in the morning. I want to run an automated program that will extract the data once it is available. It currently check every 30 minutes. I have written a SAS program that will…
Mark
  • 1
  • 2
0
votes
1 answer

Adding date in variable name in SAS

I have a column with name total transaction. I want to add a date 4 days back from now in its name . For example if today is 20161220 so I want my variable to be renamed as total_transaction_20161216. Please suggest me a way out of my problem.
Ak204
  • 63
  • 2
  • 8
0
votes
2 answers

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll There is no row at position 0

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using…
Satyam Singh
  • 1
  • 1
  • 1
0
votes
1 answer

Extract data using SQL, analyse and write back to SQL table

I am a newbie to SAS Base, and I am struggling to create a simple program that extracts data from a table on my database, runs e.g. PROC MEANS, and writes the data back to the table. I know how to use PROC SQL (read and update tables) and PROC…
hgaronfolo
  • 331
  • 1
  • 3
  • 9
0
votes
1 answer

Using PROC SQL as an IF/ELSE IF statement

I'm trying to run the following code but it not working properly. I figured out that the problem is that each case when overwrites the next statement. So, what I need to do is a IF/ELSE IF staetment but I don't know how to do that in PROC-SQL proc…
Rods2292
  • 665
  • 2
  • 10
  • 28
0
votes
3 answers

SAS first and last obs, time collapsing

I got stuck in one task. Now I have a variable called placement which has two categories, out of home and at home (A and B for short here). A case can be placed at AAABAB sequentially. Each placement has a starting date and ending date, for…
Sophie
  • 1
  • 3
1 2 3
…
52 53