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

Filtering higher values using proc sql

I would need to filter a dataset based on the higher value of var1 per each group. I have this dataset: Var1 t avg AA 1 0.02 AA 0 0.21 BB 1 0.05 BB 0 0.20 CC 1 0.10 CC 0 0.14 Built as follows Proc sql; Select Var1, …
user12809368
1
vote
2 answers

SQL group by command

I have a table where the primary id is permno (company identifier). The table also contains year and weekly returns over that year. Finally, it has a column named 'rret' which is 1 when the weekly return is greater than average yearly return…
Rasheek Irtisam
  • 15
  • 3
1
vote
1 answer

Compare rows within group of size two

I've got the below code that works beautifully for comparing rows in a group when the first row doesnt matter. data want_Find_Change; set WORK.IA; by ID; array var[*] $ RATING; array lagvar[*] $ zRATING; array…
user2448666
  • 329
  • 1
  • 6
  • 14
1
vote
5 answers

SAS-Append data to a table and increment a flag on the resultant table

I have two tables on SAS "Table_A" and "Table_A_Archive", as part my ETL process "Table_A" is created on a daily basis and the data should be archived on "Table_A_Archive". When the data is archived on "Table_A_Archive" a a flag will be…
Sudhan
  • 287
  • 2
  • 5
  • 11
1
vote
1 answer

Meaning of "select c." in SQL

if, in proc sql in SAS I create a table and write: << proc sql; create table work.mytabbi as select c.* ,o.dldl ,o.dldl; quit; >> what does "c.*" mean in this program? Thank you in advance!
Anton Amirkhanov
  • 21
  • 1
  • 2
1
vote
1 answer

SAS PROC SQL UNION ALL - minimizes column length

I have 8 tables, all containing the same order and number of columns, while one specific column named ATTRIBUTE contains different data which is of length 4 to 25. When I use PROC SQL and UNION ALL tables, the ATTRIBUTE column data length in…
Eyal Marom
  • 281
  • 4
  • 18
1
vote
2 answers

SAS/SQL group by and keeping all rows

I have a table like this, observing the behavior of some accounts in time, here two accounts with acc_ids 1 and 22: acc_id date mob 1 Dec 13 -1 1 Jan 14 0 1 Feb 14 1 1 Mar 14 2 22 Mar 14 10 22 …
doremi
  • 141
  • 3
  • 15
1
vote
0 answers

Executing stored procedure through SAS not working the same as it does in SQL Server

I have a stored procedure that I often execute within SQL Server. Without copying hundreds of lines of code into here, it basically does the following: You enter a database and table name as parameters. The procedure then calculates summary…
al_sweets
  • 136
  • 10
1
vote
1 answer

Adding rows for missing values when grouping by multiple variables in proc sql

Like the title suggests, I'm trying to add rows for observations whose value is 0 in proc sql. When I do a frequency check, I get this: Proc Freq I do a proc sql to group by my respective categories using this code which fails to account for 0…
a_swoosh
  • 23
  • 7
1
vote
2 answers

Split a column by values into new columns SQL

I'm trying to learn SAS and I want to split the different rows of a table into different columns, to group the data. Namely, Table Detailed Num Date Type Amount A1 6/12/2018 Merc 5 A2 7/3/2014 Merc 10 A2 6/5/2014 Merc 6 …
Mariana da Costa
  • 173
  • 2
  • 12
1
vote
3 answers

Using PRXMATCH to match strings from another sas dataset

Need your assistance and guidance. Please see below *rsubmit;proc sql; connect to teradata(user=&user_id. password=&user_pwd.); create table mylib.DWH_table as select * from connection to teradata( select distinct nm from…
Chug
  • 31
  • 6
1
vote
0 answers

Randomly select observations separately for each column in SQL

I am interested in generating a completely (damaged) randomized data where observations are selected randomly (with replacement) for each field and then combined. I will need to generate a new dummy id to represent the old id as I don't want to…
AlxRd
  • 285
  • 1
  • 16
1
vote
1 answer

Error when insert Date (because data value(s) do not comply with integrity constraint _NM0002_.)

I am trying to create and insert date data into table in SAS but I get this error message. proc sql; alter table a2db.student add dob DATETIME format=datetime20.; insert into a2db.student (dob) values ('1Sep2015:0:0:0'dt); ERROR: Add/Update…
Lucy Hong
  • 91
  • 8
1
vote
1 answer

How to use Pandas to select from database and local dataframe?

Using SAS, I commonly use proc sql to create a dataset reading from a database. Later I can again use proc sql to query both from the database and from the first local dataset i created. How does one do this in Python (using Pandas)? Say in SAS, I…
eshy
  • 57
  • 8
1
vote
1 answer

Daily data not showing days with no data - need 0-dates for report also

I'm making a report data for SAS VA. Example from data: Date - Customers 01Mar2019 - 4 02Mar2019 - 5 03Mar2019 - 2 05Mar2019 - 9 So I got 0 customers on 04Mar2019. That day does not show in my report data as 0 because it's not in the original…
RR91
  • 11
  • 2
1 2 3
…
52 53