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
2 answers

SAS function min and date formatting issue

I have a table that has some basic info about patients named outpatient: ID Date A 10/04/11 B 10/18/11 C 11/30/11 In SAS when I check the Date column, I see that it has format MMDDYY8. and informat DATETIME20. For another step where I…
PinkyL
  • 341
  • 1
  • 8
  • 19
0
votes
2 answers

SQL: is where name like %columnName% possible

I am aware of using where and like this select * from Table1 join table2 on table1.column like %text% i want to extend the same logic to the column. i.e select * from Table1 join table2 on table1.column like %table2.column% This is impossible on…
maverick
  • 129
  • 1
  • 3
  • 14
0
votes
1 answer

Proc sql to Pull data from different tables recursively

I need to perform a recursive count operation on tables but here are the challenges that I am facing with. Lets say I have tables A, B, C, D, E, F, .... Z Here is the code snippet of what I have, Proc sql; create table temp as( select count(*)…
Sundar Krishnan
  • 33
  • 6
0
votes
2 answers

how to get the return codes from SAS pass-through SQL to Teradata ?

In SAS 9.2, how do I get the return codes / error messages from explicit pass-through sql to teradata? Printed in log or output or something. I already got a small query to work fine, but having some trouble with a more complex one. Debugging would…
jonnaA
  • 9
  • 2
0
votes
1 answer

Summing columns in two tables then joining tables

I have two tables set up in the same way, but with different values. Here are samples from each: Table1: Date Code Count 1/1/2015 AA 4 1/3/2015 AA 2 1/1/2015 AB 3 Table2: Date Code …
Max
  • 3
  • 1
0
votes
1 answer

Composing a SQL statement with SAS MACRO code, using %put and cats

I want to write a macro that run an append the result of 10 queries, which I generate with SAS macro code. %MACRO APPENDTEST; PROC SQL; %DO I = 1 %TO 12 CREATE TABLE WORK.APPENDTEST AS SELECT t1.OrderID, t2.Name,…
Jayesh Menon
  • 59
  • 1
  • 5
0
votes
1 answer

SAS Populate table with PROC SQL INSERT statement and do loop

This is for an epidemiologic project. I wanted to calculate disease frequency rates in the years 1961 to 2013 among different populations: men of all ages, men over 50 years of age and the same two cases for women. First, I imported a population…
s.erhardt
  • 77
  • 1
  • 3
  • 9
0
votes
3 answers

Using macro in proc sql with ODBC connection to Access database

I have a macro variable called fileName. I tried to use it in the proc sql which connects to an Access databse through ODBC. However, the code I have either has an error or its not recognizing the macro variable. Here is my code: %let…
Yolanda
  • 1
  • 1
0
votes
1 answer

SAS: How to assign the first value with a higher rank in a group to the entire group

In SAS, how can I assign the first Rating value with a higher rank in a group to the entire group. See below data set ID Rating Price Rt_Rank AN A 105 0 AN B3 200 1 IG A2 705 0 IG A 700 1 IG HY …
Aryar
  • 1
  • 1
0
votes
2 answers

Selecting Max Value from Left Join

I have two tables like below. Profile : ID Charac : ID, NAME, DATE With the above tables, I am trying to get NAME from Charac where we have max date. I am trying to do a join with proc sql by replicating the answer for mysql like below proc…
LonelySoul
  • 1,212
  • 5
  • 18
  • 45
0
votes
1 answer

sas displays flag variables with - sign

I want to create table in SAS EG which will contain flag variables (for example if client has opened current account). This flag variables is stored in dwh table and has a value 1/0. But when I want to create table in SAS from this database I get -1…
vonsel
  • 11
0
votes
1 answer

How to run/not run SAS or SQL code based on conditional output?

I have a SAS program with a macro that will output a different list of variables based on the input criteria. For example, with %MACRO(OPTION1), I get three variables, but with %MACRO(OPTION2), I get four variables. The name of all of the variables…
vdiddy
  • 85
  • 1
  • 9
0
votes
1 answer

Run proc SQL by chunk of macro variable

I need to join a table Small with a table Large in Teradata DBMS. I select small.A, B, C, D 4 columns into macro variables, but the problem is the varables will exceed buffer size often times. So, I googled the code…
ddss12
  • 91
  • 1
  • 10
0
votes
1 answer

Convert a marcro variables into macro

My code1 below creates a where clause in Proc SQL based on user prompt input('Str' for store number) I'd like to use a Macro (see sample code2 below) to replace the Macro variable. How can I make it work please? Thanks! Code 1: %global STR_COUNT…
ddss12
  • 91
  • 1
  • 10
0
votes
2 answers

Merging Tables with LIKE Operator

I have two tables with multiple columns. Now I want to merge them using like operator. My code is like following proc sql; select a.*,b.* from tera.SFTP as a,home.SFTP_1 as b where a.zip_c like ('%b.zip_extract%') ; quit; I am…
LonelySoul
  • 1,212
  • 5
  • 18
  • 45
1 2 3
…
52 53