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
1
vote
3 answers

Filter a SAS dataset to contain only identifiers given in a list

I am working in SAS Enterprise guide and have a one column SAS table that contains unique identifiers (id_list). I want to filter another SAS table to contain only observations that can be found in id_list. My code so far is: proc sql noprint; …
Martin Reindl
  • 989
  • 2
  • 15
  • 33
1
vote
0 answers

SAS Dashboard embedded in Email

I am using GKPI to create a SPEEDOMETER dashboard as shown below. How can I send this Speedometer embedded (not as an attachment) in an e-mail. PROC GKPI mode=raised; speedometer actual = &Percentage bounds=(0 .60 .80 1) / target = .85 …
DaveTech
  • 11
  • 3
1
vote
4 answers

FULL OUTER JOIN unexpected results

I have these two tables: TABLE A: ID COUNTRY CAPITAL CONTINENT 1 Slovakia Bratislava Europe 2 Senegal Dakar Africa 3 Brazil Brasilia South America 4 Wales Cardiff Europe 5 Egypt Cairo Africa TABLE B: ID …
puk789
  • 322
  • 2
  • 8
  • 28
1
vote
2 answers

Transpose a table with one row and many columns using SAS SQL

My table has only 1 row and many columns. I need to return only 1 column and many rows. It is possible to do a transpose via SAS SQL? Before: column1 column2 column3 column4 1 2 3 4 After: column 1 2 3 4
Keliimek
  • 155
  • 1
  • 1
  • 10
1
vote
1 answer

Truncating SAS LASR table

I want to truncate the table instead of drop the table when it run the code. How can I change the code, from drop existing table to truncate table? In SAS LASR table, there is a line of code like below : /* Drop existing table…
Arzozeus
  • 103
  • 2
  • 11
1
vote
1 answer

How to assign longitudinal event data to phases, using a dataset of date cutpoints?

I have a dataset that lists, say, purchase dates for some products. And a separate dataset lists cut-dates for different phases of a marketing campaign for each product. I want to assign each purchase date a phase number (1 through n), according…
Quentin
  • 5,960
  • 1
  • 13
  • 21
1
vote
3 answers

Need catx macro function - catx is limited to 200 characters in proc sql

It can be kind a dummy question but I can not find example. The case is below: proc sql; create table set1 as select catx('<', field1, field2 ....) as need_field from table; quit; With this code field need_field cuts up tо length 200, so its…
Donald William Glossfield
  • 399
  • 1
  • 6
  • 19
1
vote
1 answer

proc sql - change numeric to date

I still can't believe this is not working after reading the examples and answers on here. I have a numeric variable with say the value 20160101. This needs to be 2016-01-01. My code... input(put(numvar, Z8.), yymmdd10.) as datevar OK, the result is…
MAJ Les
  • 13
  • 1
  • 1
  • 4
1
vote
2 answers

use sas select into: statement to create global macro variables

I wish to use select into: to create a global variable in a macro module. %macro example(); proc sql noprint; select x into:DDD from aaa; quit; %mend; The problem is I wish to create a global variable DDD, in this example we can only…
Wayne
  • 33
  • 1
  • 6
1
vote
2 answers

Merging 3 tables on all possible matching variables using proc SQL in SAS

I have 3 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 main_3 ID Rep Town City 1 1 3268 …
Jason Rogers
  • 667
  • 1
  • 6
  • 19
1
vote
3 answers

sas proc sql - get min date and add 1 year

I have a dataset with IDs, and each ID has multiple dates (actually datetime). I want to use PROC SQL to get the minimum datetime and also add 1 year to the minimum. I'm trying to do this all in one PROC SQL but have been fumbling and can't get…
SunnyRJ
  • 383
  • 1
  • 7
1
vote
2 answers

Convert Proc Sql Exists Query to Data Step

I have this proc sql query in my current code. Unfortunately, I'm dealing with over 10 million records, so it takes hours to run. I've been trying to convert it to a data step, thinking it would run much quicker. However, I can't seem to get the…
UncleCross
  • 33
  • 3
1
vote
2 answers

SAS conditional logic to execute another sas program based on condition

I have a dataset naming error_table as follows. All the variables are character Errorno Error Resolution 001 login check 002 datacheck check I wanted a logic that executes a sas program If the Errorno is not in 001 and 002.…
prathyu
  • 47
  • 4
1
vote
2 answers

SAS : get the observation number of the max/min value of a variable

I am trying to get as a macro variable or a plain variable the number of the observation that is the min/max of one variable. I can get easily the value of this min/max, via a proc sql proc sql noprint; select min(variable) into :minvariable from…
Anthony Martin
  • 767
  • 1
  • 9
  • 28
1
vote
1 answer

PROC SQL Left Join vs Alter Table Processing Time

I'm trying to write some code that merges on a new column of data onto the master dataset. As this is being written as a macro, I would prefer that no new dataset is created in the process. I have achieved this using the following code: PROC…
Wolfspirit
  • 155
  • 3
  • 14
1 2 3
…
52 53