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

How to use Cat in proc sql macro?

I would like to use macro with macro variable in its name like this for example: %let column1 = "XYZ"n; %let column2 = "ZXY"n; %let column_number = 1; proc sql; create table abc as select &cats("column","&column_number") where column =…
Pavel
  • 1
  • 1
0
votes
4 answers

ERROR: Unresolved reference to table/correlation name (SAS, PROC SQL)

I got the error message "ERROR: Unresolved reference to table/correlation name for every column name when I wasn't using the full table names. Now I'm using full table names and getting the same error. Not sure what else to try - I'm very new to…
LMGagne
  • 1,636
  • 6
  • 24
  • 47
0
votes
1 answer

Rename Multiple Variables in Multiple Data sets in ONE SAS Library

Is there a way to the rename the same variables in multiple tables in ONE SAS library, where there are also other tables that do not have that table? All of the tables that have variables that need renamed have the same two characters that start…
Brad
  • 85
  • 12
0
votes
4 answers

Summarizing in SQL

I'm quite new to SQL and I'm trying to summary a table using it on SAS software. Here is the table I have to summary: policy_number item 1234 1 1234 2 1234 3 567 1 89 1 90 …
Rods2292
  • 665
  • 2
  • 10
  • 28
0
votes
1 answer

How to generate 2 line in gplot graph

After I read the code from http://support.sas.com/kb/46/723.html, I want to create 2 line which having different category, below is my data : x y Category 7 7 1 4 6 1 1 5 1 6 4 2 3.5 3 2 0.5 1 2 But I am not able to create…
Arzozeus
  • 103
  • 2
  • 11
0
votes
1 answer

SAS-Create Unique Encounters based on Minimal Difference in Dates

I am trying to sort data from two tables that pairs the closes date from table 1 with the closest date from table 2 (if exists). There may be ID-dates from table 1 that do not have a match in table 2 and vice versa. Is not guaranteed to be a 1:1…
Brad
  • 85
  • 12
0
votes
1 answer

list the values instead of the formatted values with proc sql select into

When doing a proc sql select into, I get the list of formatted values. proc sql noprint; select germ into :oklist separated by '","' from maxposition where max <=10 ;run;quit; %put oklist=("&oklist"); oklist=("B.…
stallingOne
  • 3,633
  • 3
  • 41
  • 63
0
votes
2 answers

How to order columns created using Proc SQL in SAS

I am using Proc SQL to create Teradata views. I used Execute (pass through facility) and passed the column names as using variables. But the views which are getting created do not have the columns in the order which was passed into the query. They…
Harshit Krishnakumar
  • 3
  • 3
0
votes
2 answers

Union vs. Union ALL if rows are unique

I would like to optimize performance while bringing together queries on many SAS data sets with the same metadata. At this point I have: select * from (select t1.column_a, t1.column_b from table t1) Union (select…
Andrew McComb
  • 47
  • 5
0
votes
1 answer

What is the equivalent to grouping sets in SAS Proc Sql?

I have a Teradata query that has a group by and then uses grouping sets to sum up the groups in a total line: SELECT mygroup, count(x), sum(y) FROM mytable ORDER BY GROUPING (mygroup),mygroup GROUP BY mygroup GROUPING SETS ((mygroup),()) I am now…
Stian
  • 1,221
  • 1
  • 19
  • 26
0
votes
1 answer

Joining Multiple Tables with Same Metadata

I have multiple SAS data sets with same metadata, and I would like to use proc sql to create a data set for use in Tableau. What is the best way of doing that? Ex desired outcome:
Andrew McComb
  • 47
  • 5
0
votes
1 answer

Mystery values creeping in on merge

So I have a relatively complex process which pulls down a ton of data from a netezza data warehouse. I was tasked with adding two new fields to the process and went through the usual rigamarole of adding the new fields to every sort, group, by, etc.…
Kumachorou
  • 37
  • 3
0
votes
2 answers

fuzzy merge using SAS proc sql

I have two files which I would like to match by name and I would like to take account of spelling errors by using the compged function. The names have been thoroughly cleaned and I have no other useful match variables that could be used to reduce…
James
  • 101
  • 2
0
votes
1 answer

How do I merge by more than one variable using proc SQL is SAS

I have 2 datasets in SAS: main_1 ID Rep Dose Response 1 2 34 567 1 1 45 756 2 1 35 456 3 1 56 345 main_2 ID Rep Hour Day 1 1 89 157 2 1 62 365 3 1 12 689 I can easily merge these 2 datasets first by ID…
Jason Rogers
  • 667
  • 1
  • 6
  • 19
0
votes
1 answer

Proc SQL giving different results using SAS 9.4 on SAS Grid and SAS 9.3 on PC

We are in the process of transitioning our code to the SAS Grid where we use Enterprise Guide 6.1 to run SAS 9.4. One piece of code is giving different results than what we get when running SAS 9.3 on PC. I've created an example dataset and code…
Felicia Burke
  • 3
  • 2
1 2 3
…
52 53