Hello, I am trying to using proc sql to create several tables and then append them to be one table. The problem is when I append them, some of the contexts are truncated. I know we can define the length in data step, but I am wonder if there is a way that we can define the length in proc sql or proc append?  Thanks Expecting results: Type               Category AAAAA          CCCCC BBBBBBB      CCCCCC However, the program below gives the following result: Type               Category AAAAA          CCCCC BBBBB          CCCCC %macro get_data(file=, type=, category=); proc sql inobs=20;    connect to oracle as RemOracle (path=xxxx  user=xxxx pw=xxxx);         create table &file as      select *      from connection to RemOracle        (select   &type            as  Type                    ,&category       as Category          from table         );      proc append base=All data=&file force; run;     quit; %mend; %get_data(file=file1,     type='AAAAA',       category='CCCCC'); %get_data(file=file2,     type='BBBBBBB',  category='CCCCCC'); ... View more

Hello, I created a text file, but for some reason, the headers return when they should be in one row.for example: the header should be: A  B  C  D  E  F  G  H but the flat file I have returns: A  B  C  D  E F  G  H I did notice, I got a warning saying: "WARNING: The quoted string currently being processed has become more than 262 characters long.                    You may have unbalanced quotation marks." Does this cause the problem? If so, how can I fix it? Here is my program: proc contents data = file noprint     out = get_varname(keep=name rename=(name=varname)); run; Proc sql noprint;   select varname into: Flat_head_str separated by '|'   from get_varname;   select varname into: Flat_var separated by ' '   from get_varname; FILENAME flatout "C:\SAS\test.txt"; data _NULL_;   SET file END=EOF;   FILE FLATOUT  DELIMITER='|' DSD;   IF _N_=1 THEN PUT "&Flat_HEAD_STR.";   PUT &flat_var.;   IF EOF THEN PUT "TOTAL NUMBER OF RECORDS = " _N_; RUN; Thanks ... View more

Hello, I have a big SAS dataset that has 5m records with 160 variables. I want to create a txt file and then create a zip file of it so I can send to others. Here is what I did: 1. Created a macro variable for txt file header called &flat_head. Here is how it look like:  ID|F_name|L_name|Age|DOB|Zip_code|City|........ 2. Created a macro variable &putvar for put step: ID F_name L_name Age DOB Zip_code City| 3. Created a txt file using these two macro variables. However, I'm not sure which step I did wrong. program did not work. Any suggestions on this? Thank you in advance. proc contents data = file noprint     out = test(keep=name rename=(name=varname)); run; Proc sql noprint;   select varname into: Flat_head separated by '|'   from test; %put flat_head=&flat_head.;  Data test2;   Set test END=LAST;   Length putvar $1000;   IF _N_ EQ 1 Then  putvar='';   putvar=Trim(putvar)||' '||Trim(varname);   Retain putvar;   IF LAST Then Call Symput('putvar', Trim(putvar)); Run; %Put putvar=&putvar.; data _NULL_;   SET file END=EOF;   FILE FLATOUT  DELIMITER='|' DSD; IF _N_=1 THEN PUT "&Flat_head.";   PUT &putvar.;   IF EOF THEN PUT "TOTAL NUMBER OF RECORDS = " _N_; RUN; ... View more

Hi, I got an error saying 'Missing Keyword' in proc sql. if I use libname statement to connect Oracle, then I won't get any errors;however when the connect Oracle statement inside proc sql, then error appears. Here is the program. %macro setup; data _null_; TODAY   = TODAY(); format today date9.; adm_cur_frm = put(intnx('year',today,0,'b'), date9.); adm_cur_to  = put(intnx('year',today,0,'end'),date9.); CALL SYMPUTX('adm_cur_frm',adm_cur_frm); CALL SYMPUTX('adm_cur_to',adm_cur_to); RUN; %mend; %setup; /****** The following program runs fine *********/ libname lib1  oracle schema='&schema'  defer=yes path=&path user=&user pw="&pw"; %macro get(period=); proc sql;     create table test as     select *             from lib1.chrg_itm             where adm_dt between  "&adm_cur_frm"d and "&adm_cur_to"d         ;     quit; %mend; %get(period=cur); /***** Using below program I got Missing Keyword error *****/ /***** if I remove where statement, program runs fine  *****/ PROC SQL;   CONNECT TO ORACLE   (USER=&user PASSWORD="&PW" PATH=&path); CREATE TABLE TEST  AS   SELECT * FROM CONNECTION TO ORACLE     (select *      from lib1.chrg_itm      where adm_dt between  "&adm_cur_frm"d and "&adm_cur_to"d     ); DISCONNECT FROM ORACLE;    quit; %mend; %get(period=cur); I noticed this happened even when I use PC SAS / SAS EG. I tried the following as Patric suggested, I didn't get error. data test;   format dttm date9.;   dttm="&adm_cur_frm"d; output; stop; run; For my curiosity, I'm sure both connect Oracle statements are correct, but why one works while the other doesn't work. Any thoughts about this? Thank you! ... View more

Hello, I am trying to automate program that pull data from Oracle in certain period of time, but I keep getting error. Could you please help me to see how to fix this. Thank you. %macro setup; data temp; TODAY   = TODAY(); nextday = INTNX('MONTH',TODAY,3); CURQ    = QTR(today); format today nextday date9.; if CURQ = 1 then    do;        /**** reporting current year ***/         adm_cur_frm = put(intnx('year',today, -1), mmddyy10.);         adm_cur_to  = put(intnx('year',today, -1,'end'), mmddyy10.);    end; else    do;        /**** reporting current year ***/         adm_cur_frm = put(intnx('year',today, 0), mmddyy10.);          adm_cur_to  = put(intnx('Qtr',nextday, 0,'end'), mmddyy10.);    end;  CALL SYMPUTX('adm_cur_frm',adm_cur_frm); CALL SYMPUTX('adm_cur_to',adm_cur_to); RUN; %put &adm_cur_frm; %put &adm_cur_to; %mend; %setup; libname lib1  oracle schema='&schema'  defer=yes path=&path user=&user pw="&pw"; %macro get(period=); proc sql;     create table test as     select *             from lib1.chrg_itm             where adm_dt between  to_date("&&adm_&period._frm.","mm/dd/yyyy")         and                       to_date("&&adm_&period._to.","mm/dd/yyyy")     ;     quit; %mend; %get(period=cur); ERROR: Function TO_DATE could not be located. ERROR: Function TO_DATE could not be located. ERROR: Expression using BETWEEN has components that are of different data types. The data type of 'adm_dt' is below. Type    Len     Format         Informat   Num    8       DATETIME20.    DATETIME20. I also tried the following, but the program just keep running, I have to force SAS to kill the program. where adm_dt between &&adm_&period._frm and &&adm_&period._to Another one I tried below, I got zero record back, but I know there are records in between this period, can't be zero. where pstng_dt between input(strip("&&adm_&period._frm."),mmddyy10.) and                    input(strip("&&adm_&period._to."),mmddyy10.) Any suggestion will be helpful. Thanks ... View more