DATA Step, Macro, Functions and more

Importing issue in csv file

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Importing issue in csv file

000009 //JS010 EXEC SAS,WORK='130000,70000',PARM=CAPSOUT                     
000010 //ENROLL   DD DSN=A000000.ENROLL.SASS,                                
000011 //*        DISP=(,CATLG,CATLG),UNIT=CART,VOL=(,,,40)                  
000012 //         DISP=OLD                                                   
000013 //SYSIN    DD  *                                                      
000014  OPTIONS LS=250 NOCENTER PS=32000 NOLABEL MSGLEVEL=I MPRINT MLOGIC;   
000015                                                                       
000016  ****************************************************************;    
000017  **  JS010: ENROLLMENT DATA EXTRACTION.                        **;    
000018  ****************************************************************;    
000019                                                                       
000020                                                                       
000021    PROC SQL;                                                          
000022     CONNECT TO DB2(SSID=DBR0);                                        
000023      EXECUTE(SET CURRENT DEGREE='ANY') BY DB2;                        
000024       CREATE TABLE ENROLL.DATA AS SELECT * FROM CONNECTION TO DB2     
000025        (SELECT                                                        
000026           PIWT.PAT_ENR_HIST.BENE_SSN         AS BENESSN,              
000027           PIWT.PAT_ENR_HIST.BIS_DEERS_PAT_ID AS EDIPN,                
000028           PIWT.PAT_ENR_HIST.BENE_BIRTH_DT    AS PATIENTDOB,           
000029           PIWT.PAT_ENR_HIST.BENE_FIRST_NAME  AS FIRSTNAME,            
000030           PIWT.PAT_ENR_HIST.BENE_LAST_NAME   AS LASTNAME,             
000031           PIWT.PAT_ENR_HIST.SPNSR_SSN        AS SPONSORSSN            
000032         FROM                                                          
000033           PIWT.PAT_ENR_HIST                                           
000034        );                                                             
000035    %PUT &SQLXMSG &SQLXRC;                                             
000036    QUIT;                                                              
000037                                                                       
000038 //******************************************************************* 
000039 //JS011 EXEC SAS,WORK='130000,70000',PARM=CAPSOUT                     
000040 //BENELIST DD DSN=A000000.BENELIST.CSV,DISP=SHR                       
000041 //*                                                                   
000042 //BENES    DD DSN=A000000.BENELIST.SASS,                              
000043 //*        DISP=(,CATLG,CATLG),UNIT=CART,VOL=(,,,40)                  
000044 //         DISP=OLD                                                   
000045 //SYSIN   DD  *                                                       
000046  OPTIONS LS=250 NOCENTER PS=32000 NOLABEL MSGLEVEL=I MPRINT MLOGIC;   
000047                                                                       
000048  ***************************************************************;     
000049  ** JS011: LOAD LIST OF BENEFICIARIES FROM RON'S CSV FILE.    **;     
000050  ***************************************************************;     
000051                                                                       
 000052    DATA BENES.DATA;                                                     
 000053      INFILE BENELIST DLM=',' DSD FIRSTOBS=2;                            
 000054      FORMAT SPONSORSSN $9. LAST_NAME $30. SSN $9.                       
 000055             FIRST_NAME $30. PATIENTDOB DATE9.;                          
 000056      INFORMAT PATIENTDOB MMDDYY8.;                                      
 000057      INPUT SSN $ LAST_NAME $ FIRST_NAME $ PATIENTDOB DATE9.;            
 000058      FORMAT SSN $9. SPONSORSSN $9.;                                     
 000059           IF LENGTH(SSN) = 7 THEN SPONSORSSN = "00" !! SSN;             
 000060      ELSE IF LENGTH(SSN) = 8 THEN SPONSORSSN = "0" !! SSN;              
 000061                            ELSE SPONSORSSN = SSN;                       
 000062    RUN;                                                                 
 000063    PROC PRINT DATA=BENES.DATA (OBS=200); RUN;                           
 000064                                                                         
 000065 //*******************************************************************   
 000066 //JS020 EXEC SAS,WORK='130000,70000',PARM=CAPSOUT                       
 000067 //ENROLL  DD DSN=A000000.ENROLL.SASS,DISP=SHR                           
 000068 //*                                                                     
 000069 //ENROLL2 DD DSN=A000000.ENROLL2.SASS,                                  
 000070 //*       DISP=(,CATLG,CATLG),UNIT=CART,VOL=(,,,40)                     
 000071 //        DISP=OLD                                                      
 000072 //*                                                                     
 000073 //BENES   DD DSN=A000000.BENELIST.SASS,DISP=SHR                         
 000074 //*                                                                     
 000075 //OUT1    DD DSN=A000000.OUTPUT1A.TXT,                                  
 000076 //*       DISP=(,CATLG,CATLG),UNIT=CART,VOL=(,,,40)                     
 000077 //        DISP=OLD                                                      
000078 //SYSIN   DD  *                                                         
000079  OPTIONS LS=250 NOCENTER PS=32000 NOLABEL MSGLEVEL=I MPRINT MLOGIC;     
000080                                                                         
000081  ***************************************************************;       
000082  ** JS020: PROCESS DATA.                                      **;       
000083  ***************************************************************;       
000084  ** LIMIT DATA TO BENEFICIARIES ON RON'S LIST.                **;       
000085  ***************************************************************;       
000086                                                                         
000087    PROC PRINT DATA=ENROLL.DATA                                          
000088     (OBS=100); TITLE 'ORIGINAL ENROLLMENT DATA'; RUN;                   
000089                                                                         
000090    ***DEFINE ABBREVIATED NAME FOR MERGE***;                             
000091   DATA ENROLL2;                                                         
000092    SET ENROLL.DATA;                                                     
000093        **REMOVE SPACES AND PUNCTUATION**;                               
000094     NAMECOMP=COMPRESS(FIRSTNAME,,'PS');                                 
000095    ***FIRST THREE CHARS, OR WHOLE NAME IF SHORTER**;                    
000096    NAME3=SUBSTR(NAMECOMP,MIN(3,LENGTH(NAMECOMP)));                      
000097    RUN;                                                                 
000098                                                                         
000099    DATA BENE2;                                                          
000100      SET BENES.DATA;                                                    
000101    **REMOVE SPACES AND PUNCTUATION**;                                   
000102    NAMECOMP=COMPRESS(FIRST_NAME,,'PS');                                 
000103    ***FIRST THREE CHARS, OR WHOLE NAME IF SHORTER**;                    
000104    NAME3=SUBSTR(NAMECOMP,MIN(3,LENGTH(NAMECOMP)));                      
000105    RUN;                                                                 
000106                                                                         
000107    ***SAME SORT ORDER, BY MERGE VARIABLES**;                            
000108    PROC SORT DATA=ENROLL2 OUT=ENROLL3;                                  
000109      BY SPONSORSSN PATIENTDOB NAME3;                                    
000110    RUN;                                                                 
000111                                                                         
000112    PROC SORT DATA=BENE2 OUT=BENE3;                                      
000113     BY SPONSORSSN PATIENTDOB NAME3;                                     
000114    RUN;                                                                 
000115                                                                         
000116    ***MERGE***;                                                         
000117    DATA ENROLL;                                                         
000118     MERGE ENROLL3 (IN=ENROLL)                                           
000119      BENE3 (IN=LIST);                                                   
000120     BY SPONSORSSN PATIENTDOB NAME3; **SAME AS SORT ORDER**;             
000121     IF ENROLL AND LIST;                                                 
000122     RUN;                                                                
000123                                                                         
000124                                                                         
000125  ***************************************************************;       
000126  ** OUTPUT DATA.                                              **;       
000127  ***************************************************************;       
000128                                                                         
000129    %INCLUDE 'FPCENRI.MACRO1.SAS(TILOUT1)'; * OUTPUT MACRO *;            
000130    %TILOUT1(ENROLL,OUT1);                                            
000131                                                                      
000132  ENDSAS;                                                             
000133                                                                      

Accepted Solutions
Solution
‎10-06-2017 08:02 AM
Super User
Posts: 7,866

Re: Importing issue in csv file

The file was not copied to the mainframe with 'text' mode, but with binary mode, and has retained ASCII encoding.

 

Transfer the file with text mode, which automatically converts. It also takes care of linebreaks. Currently you have the 0D0A (Windows CRLF) in your data stream, which should not happen. It shows that the MF does not break records along the original lines.

 

Consult your MF people on how to transfer text files to the MF, so that they are readable from TSO with standard MF tools (file browse).

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 19,878

Re: Importing issue in csv file

LOG? Question?
SAS Super FREQ
Posts: 8,869

Re: Importing issue in csv file

Hi:

  You do not say what the issue is or show your log or provide any sample data. My take on this is that your problem is with this step in the JCL:

000038 //******************************************************************* 
000039 //JS011 EXEC SAS,WORK='130000,70000',PARM=CAPSOUT                     
000040 //BENELIST DD DSN=A000000.BENELIST.CSV,DISP=SHR                       
000041 //*                                                                   
000042 //BENES    DD DSN=A000000.BENELIST.SASS,                              
000043 //*        DISP=(,CATLG,CATLG),UNIT=CART,VOL=(,,,40)                  
000044 //         DISP=OLD                                                   
000045 //SYSIN   DD  *                                                       
000046  OPTIONS LS=250 NOCENTER PS=32000 NOLABEL MSGLEVEL=I MPRINT MLOGIC;   
000047                                                                       
000048  ***************************************************************;     
000049  ** JS011: LOAD LIST OF BENEFICIARIES FROM RON'S CSV FILE.    **;     
000050  ***************************************************************;     
000051                                                                       
000052    DATA BENES.DATA;                                                     
000053      INFILE BENELIST DLM=',' DSD FIRSTOBS=2;                            
000054      FORMAT SPONSORSSN $9. LAST_NAME $30. SSN $9.                       
000055             FIRST_NAME $30. PATIENTDOB DATE9.;                          
000056      INFORMAT PATIENTDOB MMDDYY8.;                                      
000057      INPUT SSN $ LAST_NAME $ FIRST_NAME $ PATIENTDOB DATE9.;            
000058      FORMAT SSN $9. SPONSORSSN $9.;                                     
000059           IF LENGTH(SSN) = 7 THEN SPONSORSSN = "00" !! SSN;             
000060      ELSE IF LENGTH(SSN) = 8 THEN SPONSORSSN = "0" !! SSN;              
000061                            ELSE SPONSORSSN = SSN;                       
000062    RUN;                                                                 
000063    PROC PRINT DATA=BENES.DATA (OBS=200); RUN;     

  Without a LOG, or the DATA, it is hard to figure out what your issue is and to make constructive comments. I cannot run code on the mainframe. So if you are having JCL errors, you might want to work with Tech Support on this question.

 

  My only comment is that if you have 2 commas next to each other and you want them to be treated as though there is a missing for that column, you might want to add the DSD option in the INFILE statement.

 

  Otherwise, without some direction, data or log, it is hard to understand what your issue is.

 

cynthia

Contributor
Posts: 46

LOG for Importing csv file: JS011

Posted in reply to Cynthia_sas
1           OPTIONS LS=250 NOCENTER PS=32000 NOLABEL MSGLEVEL=I MPRINT MLOGIC;  
2                                                                               
3           ***************************************************************;    
4           ** JS011: LOAD LIST OF BENEFICIARIES FROM RON'S CSV FILE.    **;    
5           ***************************************************************;    
6                                                                               
7             DATA BENES.DATA;                                                  
8               INFILE BENELIST DLM=',' DSD FIRSTOBS=2;                         
9               FORMAT SPONSORSSN $9. LAST_NAME $30. SSN $9.                    
10                     FIRST_NAME $30. PATIENTDOB DATE9.;                       
11              INFORMAT PATIENTDOB MMDDYY8.;                                   
12              INPUT SSN $ LAST_NAME $ FIRST_NAME $ PATIENTDOB DATE9.;         
13              FORMAT SSN $9. SPONSORSSN $9.;                                  
14                   IF LENGTH(SSN) = 7 THEN SPONSORSSN = "00" !! SSN;          
15              ELSE IF LENGTH(SSN) = 8 THEN SPONSORSSN = "0" !! SSN;           
16                                    ELSE SPONSORSSN = SSN;                    
17            RUN;                                                              
                                                                                
NOTE: THE INFILE BENELIST IS:                                                   
      DSNAME=A082633.BENELIST.CSV,                                              
      UNIT=3390,VOLUME=TST304,DISP=SHR,BLKSIZE=27998,                           
      LRECL=256,RECFM=VB                                                        
                                                                                
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 5 1-9.                                
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7
                                                                                
5   CHAR  |( Ë.....................ËÉÍ ÊÁ.<Á|ÈÑË.....................Â|ÀÑÃ|ÊÀ.ÊÑ
    ZONE  4445233233233003333333332555454244454523323323300333333333244444454254
    NUMR  FD13C02F25F61DA422880939C315125CC5F493C06F04F60DA247115479C2F496F24C29
                                                                                
     101  .ÊÑÄÇ ÊÀ.....................¢ ÅÁÊ.¢ÁÃÃÊÁß.....................¢|Ç+ËÈ|
    ZONE  2544445423323323300333333333244445244445452332332330033333333324444554
    NUMR  C2938124C08F21F64DA527438848CA1752CA566259C07F03F64DA435764989CAF8E34F
     201  À ÎÑË.¢Á +ÁÈÈÁ.....................Ç ((|+ÀË.ËÈ +<Áß.                  
    ZONE  4454524444455423323323300333333333244444445255444452                  
    NUMR  41693CA51E5445C02F13F60DA420065232C81DDFE43C341EC59C                  
SPONSORSSN=......... LAST_NAME=.........|Ä|++|Ê.¢Á ++Á....... SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 9 1-9.                                
                                                                                
9   CHAR  |.....................Áß.È|ÀÀ.....................Ç ÑÅÇÈ.Ê|ÂÁÊÈ.......
    ZONE  4233233233003333333324452544423323323300333333333244444525444552332332
    NUMR  FC01F12F55DA45542822CB59C4F44C07F22F64DA369920952C819784C2F2524C08F15F
                                                                                
     101  ..................|Á+ÑÈ!ÁÊ.Å Êß.....................Å< ËÅ|Ï.À Ï+......
    ZONE  3323300333333333244444554524455233233233003333333332444544524454233233
    NUMR  08F57DA393468040CBF5E94A52C7129C07F24F59DA478725873C7C137F7C417EC08F10
                                                                                
     201   .....................Ç (Ñ<È|+.Ä ÈÇß................                  
    ZONE  4233233233003333333332444445442445452332332330033333                  
    NUMR  1C07F24F54DA462252202C81D9C4FEC31489C05F19F61DA18956                  
SPONSORSSN=......... LAST_NAME=.......Ä ËËÑÀß.ÈÇ|( Ë......... SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 13 1-9.                               
                                                                                
13  CHAR  . <ΠÊÁ!.<Ñ!! .....................(Á+ÀÑ|< .Å<|ÊÑ ....................
    ZONE  2445454524455423323323300333333333244444444244454423323323300333333333
    NUMR  C1C6125ACC9AA1C07F21F60DA586885297CD5E49FC1C7CF291C09F21F57DA245726439
                                                                                
     101  ....Ç <<|Ä.. Ê ʠ.....................¢ Ä.Ë|+.ÁÀÏ ÊÀ................
    ZONE  3332444444424454454233233233003333333332444454424454542332332330033333
    NUMR  302C81CCF3BC2122121C12F09F58DA161564181CA13B3FEC547124C12F08F65DA22719
                                                                                
Contributor
Posts: 46

Re: Importing issue in csv file

Posted in reply to Cynthia_sas

I posted the log for JS011. Basically, when I import it's not reading the PATIENTDOB (date of Birth) or anything else in the file.

NOTE: THE SASUSER LIBRARY WAS NOT SPECIFIED. SASUSER LIBRARY WILL NOW BE THE SAM
NOTE: ALL DATA SETS AND CATALOGS IN THE SASUSER LIBRARY WILL BE DELETED AT THE E
      PREVENT THEIR DELETION.                                                   
                                                                                
NOTE: SAS SYSTEM OPTIONS SPECIFIED ARE:                                         
      CAPSOUT                                                                   
                                                                                
NOTE: 1909456896 BYTES WERE AVAILABLE ABOVE THE LINE AT INITIALIZATION.         
                                                                                
NOTE: 10461184 BYTES WERE AVAILABLE BELOW THE LINE AT INITIALIZATION.           
                                                                                
NOTE: 1908932608 BYTES WERE AVAILABLE ABOVE THE LINE AFTER ADJUSTMENT FOR MEMLEA
                                                                                
NOTE: THE INITIALIZATION PHASE USED 0.03 CPU SECONDS AND 12958K.                
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 13384K ABO
                                                                                
1           OPTIONS LS=250 NOCENTER PS=32000 NOLABEL MSGLEVEL=I MPRINT MLOGIC;  
2                                                                               
3           ***************************************************************;    
4           ** JS011: LOAD LIST OF BENEFICIARIES FROM RON'S CSV FILE.    **;    
5           ***************************************************************;    
 BROWSE -- LIST.J26977.SAS.JS011.SASLOG ------------- Line 00000056 Col 001 080 
7             DATA BENES.DATA;                                                  
8               INFILE BENELIST DLM=',' DSD FIRSTOBS=2;                         
9               FORMAT SPONSORSSN $9. LAST_NAME $30. SSN $9.                    
10                     FIRST_NAME $30. PATIENTDOB DATE9.;                       
11              INFORMAT PATIENTDOB MMDDYY8.;                                   
12              INPUT SSN $ LAST_NAME $ FIRST_NAME $ PATIENTDOB DATE9.;         
13              FORMAT SSN $9. SPONSORSSN $9.;                                  
14                   IF LENGTH(SSN) = 7 THEN SPONSORSSN = "00" !! SSN;          
15              ELSE IF LENGTH(SSN) = 8 THEN SPONSORSSN = "0" !! SSN;           
16                                    ELSE SPONSORSSN = SSN;                    
17            RUN;                                                              
                                                                                
NOTE: THE INFILE BENELIST IS:                                                   
      DSNAME=A082633.BENELIST.CSV,                                              
      UNIT=3390,VOLUME=TST304,DISP=SHR,BLKSIZE=27998,                           
      LRECL=256,RECFM=VB                                                        
                                                                                
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 5 1-9.                                
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7
                                                                                
5   CHAR  |( Ë.....................ËÉÍ ÊÁ.<Á|ÈÑË.....................Â|ÀÑÃ|ÊÀ.ÊÑ
    ZONE  4445233233233003333333332555454244454523323323300333333333244444454254
    NUMR  FD13C02F25F61DA422880939C315125CC5F493C06F04F60DA247115479C2F496F24C29
                                                                                
     101  .ÊÑÄÇ ÊÀ.....................¢ ÅÁÊ.¢ÁÃÃÊÁß.....................¢|Ç+ËÈ|
    ZONE  2544445423323323300333333333244445244445452332332330033333333324444554
    NUMR  C2938124C08F21F64DA527438848CA1752CA566259C07F03F64DA435764989CAF8E34F
     201  À ÎÑË.¢Á +ÁÈÈÁ.....................Ç ((|+ÀË.ËÈ +<Áß.                  
    ZONE  4454524444455423323323300333333333244444445255444452                  
    NUMR  41693CA51E5445C02F13F60DA420065232C81DDFE43C341EC59C                  
SPONSORSSN=......... LAST_NAME=.........|Ä|++|Ê.¢Á ++Á....... SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 9 1-9.                                
                                                                                
9   CHAR  |.....................Áß.È|ÀÀ.....................Ç ÑÅÇÈ.Ê|ÂÁÊÈ.......
    ZONE  4233233233003333333324452544423323323300333333333244444525444552332332
    NUMR  FC01F12F55DA45542822CB59C4F44C07F22F64DA369920952C819784C2F2524C08F15F
                                                                                
     101  ..................|Á+ÑÈ!ÁÊ.Å Êß.....................Å< ËÅ|Ï.À Ï+......
    ZONE  3323300333333333244444554524455233233233003333333332444544524454233233
    NUMR  08F57DA393468040CBF5E94A52C7129C07F24F59DA478725873C7C137F7C417EC08F10
                                                                                
     201   .....................Ç (Ñ<È|+.Ä ÈÇß................                  
    ZONE  4233233233003333333332444445442445452332332330033333                  
    NUMR  1C07F24F54DA462252202C81D9C4FEC31489C05F19F61DA18956                  
SPONSORSSN=......... LAST_NAME=.......Ä ËËÑÀß.ÈÇ|( Ë......... SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 13 1-9.                               
                                                                                
13  CHAR  . <ΠÊÁ!.<Ñ!! .....................(Á+ÀÑ|< .Å<|ÊÑ ....................
    ZONE  2445454524455423323323300333333333244444444244454423323323300333333333
    NUMR  C1C6125ACC9AA1C07F21F60DA586885297CD5E49FC1C7CF291C09F21F57DA245726439
                                                                                
     101  ....Ç <<|Ä.. Ê ʠ.....................¢ Ä.Ë|+.ÁÀÏ ÊÀ................
    ZONE  3332444444424454454233233233003333333332444454424454542332332330033333
    NUMR  302C81CCF3BC2122121C12F09F58DA161564181CA13B3FEC547124C12F08F65DA22719
     201  ........È|ËÈ|.ËÁ ËÈÑ +.....................Ê (ÑÊÁ!.                  
    ZONE  3333333254554254445544423323323300333333333254445452                  
    NUMR  5465952C4F34FC35213491EC02F08F53DA464473204C21D925AC                  
SPONSORSSN=.....ËÍ!Í LAST_NAME=( ÊÁ<<|.ËÈÁ&ÇÁ+.............. SSN=.....ËÍ!Í FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 17 1-9.                               
                                                                                
17  CHAR  ÍÁËÈ À.ÊÁÂÁÄÄ .....................ÅÑÂË|+.(ÑÈÄÇÁ<<....................
    ZONE  5455442544444423323323300333333333244454424454444423323323300333333333
    NUMR  553414C2525331C02F23F61DA125487574C7923FECD94385CCC07F16F56DA554591672
                                                                                
     101  .......ÃÊßÁÊ.Å ÊÊÑÄ......................ÏÑ<<Ñ (Ë.ËÇÁÑ< ..............
    ZONE  3333332455452445544423323323300333333333254444445254444423323323300333
    NUMR  948243C62952C712293BC03F22F61DA444884643C79CC91D3C3859C1C05F22F60DA995
                                                                                
     201  .........(Ñ<<ÁÊ.Á<Ñ! ÂÁÈÇ.....................Ç|&.Ñ+                  
    ZONE  3333333324444452444544454233233233003333333332445444                  
    NUMR  16949024CD9CC52C5C9A12548C08F04F65DA221563200C8F0B9E                  
SPONSORSSN=Ç|<<ß.... LAST_NAME=............ÄÇÁÏ<|ÏÁÊß.¢ ÄÉÍÁ< SSN=Ç|<<ß.... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 21 1-9.                               
                                                                                
21  CHAR   <<|Ë.Ë +ÀÊ .....................Å ÃÃ+Áß..Á<<ß.....................ÏÑÅ
    ZONE  4444525444542332332330033333333324444445244445233233233003333333332544
    NUMR  1CCF3C31E421C10F11F52DA156641975C7166E59CB5CC9C09F29F62DA419929226C797
                                                                                
     101  ..Ê|( | .¢Í + .....................¢ÁÏÁ<<.ÁÀÏ ÊÀ.....................
    ZONE  3254444442454442332332330033333333324454442445454233233233003333333332
    NUMR  5C2FD21F1CA51E1C03F19F57DA158428356CA575CCC547124C10F21F54DA411130232C
                                                                                
     201  ....& Ê.ÁÊ.(|<<ß.....................ÊÁ++ÁÊ.<ÑË ++Á.                  
    ZONE  3332545445244445233233233003333333332544445244544442                  
    NUMR  858C012B52CDFCC9C02F08F64DA476729625C25EE52CC931EE5C                  
SPONSORSSN=Ë..Á<<ß.. LAST_NAME=...........ÑÊÏÑ+.¢ÍÀÑÈÇ....... SSN=Ë..Á<<ß.. FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 25 1-9.                               
                                                                                
25  CHAR  +. +ÅÁ<ÑÄ .....................Ç ß+.ÇÁ<Á+.....................Ä< Ê..(Ñ
    ZONE  4244444444233233233003333333332445424444423323323300333333333244454244
    NUMR  EC1E75C931C05F20F63DA209406523C819EC85C5EC08F29F53DA481840689C3C12BCD9
                                                                                
     101  È .Ëß<ÎÑ .....................ÄÇÑ<ÁË.( ÊÅ ÊÁÈ...................ÄÇ &ÀÁ
    ZONE  5425545442332332330033333333324444452445445452332332330033333332444544
    NUMR  41C39C691C02F24F64DA579723761C389C53CD1271254C05F30F53DA2586849C381045
                                                                                
     201  ...Å|ÁÇÊÑ+Å.ÅÊÁÅ|Êß.....................Ë. Ñ+ÁË.ÁÑ<Á                  
    ZONE  3324444544424544455233233233003333333332544444524444                  
    NUMR  19C7F5829E7C7257F29C12F07F57DA462419987C3B19E53C59C5                  
SPONSORSSN=......... LAST_NAME=.............(ÑÈÄÇÁ<<.ÀÑ +Á... SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 29 1-9.                               
                                                                                
29  CHAR  . ÊÊß.....................ÇÑ<ÀÏÑ+Á.¢Ñ+ÇÁÁ.....................Â|ÅÅË.È
    ZONE  2445552332332330033333333324444544424444442332332330033333333324444525
    NUMR  C21229C05F08F58DA162366329C89C479E5CA9E855C02F05F55DA391844785C2F773C4
                                                                                
     101  Â|<À. Ê ʠ.....................(Á +Ë.ÂÁÎÁÊ<ß....................ÅÍ Â
    ZONE  4444244544542332332330033333333324444524454545233233233003333333324544
    NUMR  2FC4C2122121C01F05F54DA425333715CD51E3C25652C9C08F21F65DA70581265C7512
     201  .......(Á+ÀÁ!.Ê|Ë .....................Ï||ÀË..ÁÎÑ+..                  
    ZONE  3333332444445254542332332330033333333325444524454423                  
    NUMR  232391CD5E45AC2F31C04F02F63DA310802741C7FF43CB569EC0                  
SPONSORSSN=Á+....... LAST_NAME=............Ã|Ì.<Ñ+À ......... SSN=Á+....... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 33 1-9.                               
                                                                                
33  CHAR  ¢|Ç+.....................ÊÑÄÇ ÊÀË|+.Ñ.Á.....................Ç ßÏ||À.ÈÇ
    ZONE  4444233233233003333333332544445454424442332332330033333333324455444254
    NUMR  AF8EC11F16F62DA418986092C29381243FEC9B5C04F12F63DA212846430C8197FF4C48
                                                                                
     101  ÁÊË|+.ÂÊÁ+À .....................ÎÁÊÀÁ¢|.ÂÁÊËÑË.....................&Á
    ZONE  4554424544442332332330033333333325454444244554523323323300333333333254
    NUMR  523FEC225E41C04F05F54DA584022948C65245AFC252393C09F06F56DA177386241C05
                                                                                
     201  ¢Á+ËÁ+.À ÎÑÀ.....................Ë&Á+ÄÁÊ.( ÊÎÑ+.....                  
    ZONE  4445442445442332332330033333333325544445244554423323                  
    NUMR  A5E35EC41694C05F16F57DA578828804C305E352CD1269EC07F3                  
SPONSORSSN=......... LAST_NAME=....................ÂÊ|Ï+. ËÑ SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 37 1-9.                               
                                                                                
37  CHAR  ....................Ç|ÊÈ|+.Π<ÁÊÑÁ..................... ÊÀ|Ë. +ÀÊÁ ...
    ZONE  3323323300333333333244554425444544233233233003333333324454452444544233
    NUMR  12F15F62DA255063740C8F24FEC61C5295C01F16F65DA41545816CB124F3C1E4251C08
                                                                                
     101  .....................( ÀËÁ+. Ê<ß+....................ÃÑÁ<À.Ê|ÂÁÊÈ.....
    ZONE  2332332330033333333324445442454542332332330033333333244444254445523323
    NUMR  C12F28F65DA214805827CD1435EC12C9EC07F15F63DA86649104C695C4C2F2524C01F0
                                                                                
     201  Á ÊÂßÏ ÀÁ.¢ ÄÉÍÁ<Ñ+Á.....................( ÈÁ|.Ê|ÀÊÑ                  
    ZONE  4454554442444554444423323323300333333333244544254454                  
    NUMR  512297145CA13155C9E5C01F02F65DA569295926CD145FC2F429                  
SPONSORSSN=...ÁΠ+Ë. LAST_NAME=.....................Ï È.Ñ+Ë.Ê SSN=...ÁΠ+Ë. FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 73 1-9.                               
                                                                                
73  CHAR  .ÃÁÊ+ +À|.....................<|+Å.À|+ <À.....................+Ñ!Á.< Í
    ZONE  2445444442332332330033333333324444244444423323323300333333332444542445
    NUMR  C652E1E4FC10F14F64DA227048797CCFE7C4FE1C4C11F22F60DA81565985CBE9A5CC15
                                                                                
     101  & (Á< .....................ÂÊ|Ï+.& ÈÊÑÄÑ .....................(Ñ<<ÁÊ..
    ZONE  5444442332332330033333333324545425455444423323323300333333333244444524
    NUMR  01D5C1C04F06F64DA527252203C22F7EC01429391C10F05F65DA198521933CD9CC52CB
                                                                                
     201  +Ë.&ÇÑ<<Ñ&.....................+Á <.ËÇ Ê|+..........                  
    ZONE  4525444445233233233003333333332444425445442332332330                  
    NUMR  E3C089CC90C07F25F55DA311681868CE51CC3812FEC05F25F57D                  
SPONSORSSN=Å|....... LAST_NAME=.............. ÊÄ< ß.&ÁÈÁÊ... SSN=Å|....... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 77 1-9.                               
                                                                                
77  CHAR  ........ÂÊß +È.(ÑÄÇ Á<.....................Á(ÁÊË|+.ÇÁÊÂÁÊÈ............
    ZONE  3333333245544524444444233233233003333333332444554424454455233233233003
    NUMR  9049013C2291E4CD93815CC12F31F56DA227867096C5D523FEC8522524C02F25F58DA3
                                                                                
     101  .............(ÄÏÑ<<Ñ (Ë.¢Á++ÑÁ.....................ÂÊÁÁÀÁ+.. ÈÇß......
    ZONE  3003333333332445444444524444442332332330033333333324544444244545233233
    NUMR  1DA579746907CD379CC91D3CA5EE95C11F19F65DA238139999C225545ECB1489C01F20
                                                                                
     201  ...................ÂÍ<<Á+.+ÑÊ <ß++..................                  
    ZONE  3233233003333333332454444244544544233233233003333333                  
    NUMR  9F30F60DA302702760C25CC5ECE921C9EEC02F07F64DA1656282                  
SPONSORSSN=......... LAST_NAME=.ÈÇ|Ê&Á.ÈÍÄ.ÁÊ.ÂÁÈÈÑ+ ........ SSN=......... FIRS
NOTE: INVALID DATA FOR PATIENTDOB IN LINE 81 1-9.                               
ERROR: LIMIT SET BY ERRORS= OPTION REACHED.  FURTHER ERRORS OF THIS TYPE WILL NO
ERROR: LIMIT SET BY ERRORS= OPTION REACHED.  FURTHER ERRORS OF THIS TYPE WILL NO
ERROR: LIMIT SET BY ERRORS= OPTION REACHED.  FURTHER ERRORS OF THIS TYPE WILL NO
                                                                                
81  CHAR  .... ÊÈ|+.& (Á< .....................ÇÁ+ÀÁÊË|+.¢ (ÁË.................
    ZONE  3332445544254444423323323300333333333244444554424444523323323300333333
    NUMR  993C2124FEC01D5C1C07F13F54DA546431703C85E4523FECA1D53C01F08F57DA784856
                                                                                
     101  ..........Ê|Ë.ÊÍÈÇ.....................|Ê<|ÎË.ß.¢|Ç+..................
    ZONE  3333333332545255542332332330033333333324544554524444233233233003333333
    NUMR  568293240C2F3C2548C06F21F55DA398487596CF2CF63B9CAF8EC11F24F57DA5564518
                                                                                
     201  .......Ë +ÈÁÁ.ÀÁÂÊ .....................ËÏ +Ë|+.ÁÊ+Á                  
    ZONE  3333332544544244454233233233003333333332554454424544                  
    NUMR  988050C31E455C45221C10F30F52DA281703824C371E3FEC52E5                  
SPONSORSSN=...(|ÊËÁ. LAST_NAME=Ë.....................ÁΠ+Ë.ÎÑ SSN=...(|ÊËÁ. FIRS
NOTE: LOST CARD.                                                                
NOTE: 4386 RECORDS WERE READ FROM THE INFILE BENELIST.                          
      THE MINIMUM RECORD LENGTH WAS 167.                                        
      THE MAXIMUM RECORD LENGTH WAS 252.                                        
NOTE: SAS WENT TO A NEW LINE WHEN INPUT STATEMENT REACHED PAST THE END OF A LINE
NOTE: THE DATA SET BENES.DATA HAS 1096 OBSERVATIONS AND 5 VARIABLES.            
NOTE: THE DATA STATEMENT USED 0.02 CPU SECONDS AND 14513K.                      
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 14956K ABO
                                                                                
                                                                                
18            PROC PRINT DATA=BENES.DATA (OBS=200); RUN;                        
                                                                                
NOTE: THERE WERE 200 OBSERVATIONS READ FROM THE DATA SET BENES.DATA.            
NOTE: THE PROCEDURE PRINT PRINTED PAGE 1.                                       
NOTE: THE PROCEDURE PRINT USED 0.02 CPU SECONDS AND 17984K.                     
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 18432K ABO
                                                                                
                                                                                
19                                                                              
                                                                                
ERROR: ERRORS PRINTED ON PAGE 1.                                                
ERROR: ERRORS PRINTED ON PAGE 1.                                                
ERROR: ERRORS PRINTED ON PAGE 1.                                                
                                                                                
NOTE: THE SAS SESSION USED 0.07 CPU SECONDS AND 17984K.                         
                                                                                
NOTE: THE ADDRESS SPACE HAS USED A MAXIMUM OF 636K BELOW THE LINE AND 18432K ABO
                                                                                
NOTE: SAS INSTITUTE INC., SAS CAMPUS DRIVE, CARY, NC USA 27513-2414             
******************************** Bottom of Data ********************************                                                                                
                                                                                
Super User
Posts: 19,878

Re: Importing issue in csv file

Include some representative data that can be used to test your code.

Solution
‎10-06-2017 08:02 AM
Super User
Posts: 7,866

Re: Importing issue in csv file

The file was not copied to the mainframe with 'text' mode, but with binary mode, and has retained ASCII encoding.

 

Transfer the file with text mode, which automatically converts. It also takes care of linebreaks. Currently you have the 0D0A (Windows CRLF) in your data stream, which should not happen. It shows that the MF does not break records along the original lines.

 

Consult your MF people on how to transfer text files to the MF, so that they are readable from TSO with standard MF tools (file browse).

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 100

Re: Importing issue in csv file

Try this.....

 

 

 000052    DATA BENES.DATA;                                                     
 000053      INFILE BENELIST DLM=',' DSD FIRSTOBS=2;                            
 000054      FORMAT SPONSORSSN $9. LAST_NAME $30. SSN $9.                       
 000055             FIRST_NAME $30. PATIENTDOB DATE9.;                          
 000056      INFORMAT PATIENTDOB MMDDYY8.;                                      
 000057      INPUT SSN: $ 
LAST_NAME: $ FIRST_NAME: $ PATIENTDOB: DATE9.;            
 000058      FORMAT SSN $9. SPONSORSSN $9.;                                     
 000059           IF LENGTH(SSN) = 7 THEN SPONSORSSN = "00" !! SSN;             
 000060      ELSE IF LENGTH(SSN) = 8 THEN SPONSORSSN = "0" !! SSN;              
 000061                            ELSE SPONSORSSN = SSN;                       
 000062    RUN; 

I simply placed ":" for each variable stated in your input statement. 

 

Hope this helps.

Super User
Super User
Posts: 7,080

Re: Importing issue in csv file

[ Edited ]

Are you sure that your data file is actually saved as using EBCIDIC coding?  I do not see any commas in the data dump that SAS made but I do see some bytes with '2C'x which is the ASCII code for a comma.

 

You could try using DLM='2C'x on your INFILE statement in case just the delimiters are using the wrong encoding.

 

Also you seem a little confused about what format PATIENTDOB will be coming in. You used DATE9 informat on the INPUT statement but also attached the MMDDYY informat to the variable.

 

Or you might try converting the whole line from ASCII to EBCDIC before trying to read it.

 

data benes.data;
  infile benelist dlm=',' dsd firstobs=2 truncover;
  input @;
  _infile_ = put(_infile_,$ASCII80.);
  length sponsorssn $9 last_name $30 ssn $9
         first_name $30 patientdob 8
  ;
  format patientdob date9. ;
  informat patientdob date9. ;
  *informat patientdob mmddyy8.;
  input ssn last_name first_name patientdob ;
  sponsorssn=put(input(ssn,9.),z9.);
run;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 157 views
  • 0 likes
  • 6 in conversation