comp.soft-sys.sas - The SAS statistics package.
Hi, Could I have some advice please as I need to convert a datetime field to a date but I cannot use datepart() and need to use PROC SQL. Would it be something like this substr(to_char(mydatetimefield,n,n,)) Thanks Lee
This code is a tad confusing, looks like fragments, not a cohesive program : 1) there's no need to create temp table NB, why not use NEWBRN, it's exactly the same 2) &vars isn't used 3) presumably the data step where gdg&I is create is part of a macro ( loop? ), but the same input dataset is read in each case, is there a WHERE clause you've not provided? 4) the WHERE statement in the SQL step where MATCHES is created does not need the IN clause, should be specified: where A.P_OFFICE = b.OFFICE AND A.TRNFR_DY >. ; 5) proc summary uses RPT, an entirely different dataset than MATCHES created immediately above it -----Original Message----- From: XXXX@XXXXX.COM [mailto: XXXX@XXXXX.COM ] On Behalf Of Jumbo Shrimps, Jr. Sent: 2007, April, 07 11:30 AM To: XXXX@XXXXX.COM Subject: Proc SQL returning data not in selection Step 1. Read in couple hundred office numbers: and create temp table NB DATA NEWBRN; INFILE NEWBRN DELIMITER=','; INPUT OFFICE $ OPENDATE MMDDYY8.; RUN; PROC SQL; CREATE TABLE NB AS SELECT * FROM NEWBRN ; Step 2. Cycle through bunch of GDG data sets containing office data such as sales, deposits, transfers, etc. using only these variables: %LET VARS=SALE_DY SALESREP ACCT ORIGIN P_OFFICE BALANCE AMOUNT SSN ACCTN OPEN TRNFR_DY TFERIAMT ; DATA GDG&I. ; SET IN.A; This SQL matches the couple hundred list of offices in table NB to the huge dataset of all offices with a couple hundred variables, (but I'm only using a dozen) matching by GDG.P_OFFICE in the GDG datasets (OUT.A) to the list of offices in NB grabbing the variables from the %LET VARS: PROC SQL; CREATE TABLE MATCHES AS SELECT A.*, B.* FROM OUT.A A, NB B WHERE A.P_OFFICE IN (SELECT OFFICE FROM NB) AND A.TRNFR_DY >. ; However, when I run the Proc summary below: PROC SUMMARY DATA=RPT NWAY; CLASS OFFICE TRNFR_DY; VAR BALANCE ACCT TFERIAMT; OUTPUT OUT=SUMM SUM=; RUN; I get several hundred offices with summary data, NOT in my read in list from step 1. How can that be? I only want the data that pertains to table "NB" matched to the huge GDG data sets by GDG. P_OFFICE to NB.OFFICE. Could it be WHERE A.P_OFFICE IN (SELECT OFFICE FROM NB) AND A.TRNFR_DY >. ; which is spozed to grab all those matching offices where there was a transfer, but the SQL must be returning all offices that had a non-blank TRNFR_D regardless of whether the office was in my list. _______________________________________________________________________ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courrier ectronique est confidentiel et prot L'expiteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) digns) est interdite. Si vous recevez ce courrier ectronique par erreur, veuillez m'en aviser immiatement, par retour de courrier ectronique ou par un autre moyen.
Hello, Does anybody know, is it possible to select multiple rows in TableViewer control? I mean not just selecting a sequence of rows (like when using SHIFT key) but selecting several groups of rows (like with CTRL). I'm using SAS v9.1.3. Will be grateful for any help. Thanks, Konstantin
Hello, Here's my conundrum. I'm fairly new at SAS so I'm having a difficult time figuring this one out. I posted this question before and got several suggestions to the first part regarding matching tables but the second part (pulling 10 observations) is still vexing me. Here's some sample data: In the first dataset I have a transaction date and a unique company id: Date SEDOL 01/02/1985 1234567 01/04/1985 3456789 01/05/1985 4534534 In the second dataset I have a date, SEDOL, and stock price info 1234567 3456789 4534534 01/02/1985 12 19.3 41 01/03/1985 11 19 40 01/04/1985 11.5 19.1 40 01/05/1985 19 19 42 01/06/1985 14 19 42 01/09/1985 12 19 43 01/10/1985 12.5 19 40 01/11/1985 8 19.3 40 01/12/1985 12 19.9 42 01/13/1985 12 19.8 40 01/15/1985 12 19.1 44 01/16/1985 12 19.4 40 01/17/1985 12 19.2 40 01/18/1985 10 19.1 41 What I'm trying to do is pull the transaction date and SEDOL from the first dataset and match it to the second dataset and then pull the next 10 observations in the stock price. In terms of matching the two tables it was suggested that I use PROC TRANSPOSE and then match using SQL. Here's the SQL (ds1 = the first dataset, ds2 = the second dataset) that a listserv member kindly provided: proc transpose data=ds2 out=ds2b; by Date; run; * adjust cosmetics and types ; data ds2c; set ds2b; SEDOL = input(substr(_name_,2),best.); rename col1=value; drop _name_; attrib _all_ label=''; format date mmddyy10.; run; proc sql; create table result as select b.* from ds1 as a join ds2c as b on a.SEDOL eq b.SEDOL and a.date eq b.date ; quit; What I'm wondering is if pulling the 10 observations is best done in SQL or with an array? Somehow it feels like an array would be the smoother way to go but either way there's a learning curve for me for both options so any advice on relevant functions and code structure would be appreciated!