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

Querying using the datepart from a time stamp in a pass-through Proc SQL

I am trying to use the date part of a time-stamp in my where query in a db2 pass-through proc SQL code below. I tried using date and datepart functions but it wont work with this format. Does anyone know the name of the function to use in the same…
Keneni
  • 705
  • 5
  • 12
0
votes
0 answers

SAS proc sql SELECT DISTINCT fails to remove duplicates on already sorted dataset

Given the following test data: data test; input A B; cards; 1 2 1 1 1 2 run; NOTE: The data set WORK.TEST has 3 observations and 2 variables. I am aware that proc sort can behave unexpectedly if you don't sort by a whole key, or even when you…
jl6
  • 6,110
  • 7
  • 35
  • 65
0
votes
1 answer

SAS - Automate Change in Data Source to Provide Report

I currently have a program that Generates a full report (Data Manipulation, Analysis, Formatting, Excel output) based on a few internal parameters. I would like to build a tool or Macro that will iterate through 6 data sets, and provide a report…
BGDev
  • 135
  • 1
  • 8
0
votes
1 answer

confidence interval of the standard deviation with proc sql

My data set is really simple, just one colum with a ratio and another colum with a categorical var, I need to calculate the standard deviation for each class as well as the confidence interval. Is there a built in function in SAS (proc SQL) to…
Soly
  • 111
  • 2
  • 8
0
votes
1 answer

SQL Group By in SAS - alternative?

rsubmit; proc sql; create table wpffasdekpr2 as select polnum, ordnnr, brkd, fsnr, fvpkd, mnr, mlopnr,coverfrom, coverto, nationalkey, exposuretype, COActivityCode, sum(COSumInsured) as SumIns, sum(AnnualBaseAmt) as SumAmt from wpffasdekpr group by…
DavveK
  • 173
  • 2
  • 12
0
votes
1 answer

Getting value of row that corresponds to distinct? (proc sql)

I have a dataset unique across 5 variables. The 5th is an identifier variable. Finally, I have a 6th variable, which is dependent on the identifier variable. The identifier variable can appear in multiple places. The dependent variable will never…
Nate
  • 420
  • 1
  • 5
  • 17
0
votes
2 answers

PROC SQL to remove duplicate pairs of observations?

I need to remove observations with duplicate pairs (ie. the original observation and it's duplicate). I have more than hundred columns, but sometimes for a given ID I am getting a pair of different Load_Date and a pair of Contactor columns. The…
user2993827
  • 73
  • 1
  • 2
  • 8
0
votes
4 answers

replicating a sql function in sas datastep

Hi another quick question in proc sql we have on which is used for conditional join is there something similar for sas data step for example proc sql; .... data1 left join data2 on first
user3135774
0
votes
0 answers

Union issue in SAS

I have met an issue about union in SAS. 1) When I run the code below: proc sql; select count(*) as cnt_obs, count(distinct user) as cnt_user from input1 union all select count(*) as cnt_obs, count(distinct user) as cnt_user from input2 union…
Charles Deng
  • 1
  • 1
0
votes
1 answer

Unable to resolve macro

proc sql noprint; select count(distinct USUBJID) into : N1 - : N4 from DM where upcase(ARM) ^= "SCREEN FAILURE"` group by ARMN; quit; %macro TOTAL(name=,num=); %do i=1 %to #` %if ARMN=&i %then TOTAL=…
Nimit_ZZ
  • 495
  • 4
  • 10
  • 21
0
votes
1 answer

Utility to insert row in any sas dataset

I want to create a unix utility to insert 1 row into a sas dataset. When run, this scipt will ask user to insert value for each variable in the dataset(preferabely telling him the type and length of the variable). It will then pass these values to…
siso
  • 135
  • 1
  • 1
  • 7
0
votes
1 answer

Insert Date Field to MS SQL from Proc SQL

I'd like to insert a date field into a SQL server table form Proc SQL in SAS. Here is my code for Proc SQL: proc sql; insert into CFS_SQL.Data_DSB_Raw(sasdatefmt=(TheDate='mmddyy10.')) select TheDateIncoming from…
John Chrysostom
  • 3,973
  • 1
  • 34
  • 50
0
votes
1 answer

Insert into Proc SQL Passthrough in SAS

I have a SQL Server 2012 machine hosting a database with a large amount of data that I query on a regular basis. In order to make the query, I need to upload a list of Unique IDs, then query for data related to those unique IDs. In order to keep…
John Chrysostom
  • 3,973
  • 1
  • 34
  • 50
0
votes
3 answers

aggregate to overwrite existing data

I used the following code, which works perfectly, to get the results that follow: data No_int_weeksPaid; set no_internet4; keep account_number week0-week61; by account_number; array week{62} week0-week61; do i = 1 to 62; if i > subscription_start…
user2448666
  • 329
  • 1
  • 6
  • 14
0
votes
3 answers

is there any alternates to merge two datasets in SAS apart from Proc Sql and Merge statement?

i want to merge two datasets, but without using merge statement and Proc sql,can i do this? is there any way to do the same
ved_null_
  • 71
  • 2
  • 10
1 2 3
…
52 53