cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7
Go to Solution

impot CSv data file and read the data in correct column in sas

Posted 09-02-2022 08:01 AM (2657 views)

Hello,

 

I am new to SAS need help to import & read data correctly to specific variables

 

i have raw data as below :

 

Trail noSubject idDate of Birthstudy dayCategory namePanel nameDescriptionLab Acc numberCollection DateCollection TimeMin valueMax valueUnitValue
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6739251216 May 202212:11 <0.3x10{S9}/L< 0.1
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS6739251216 May 202212:11  %1
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYHAEMATOCRIT6739251216 May 202212:110.400.55 0.42
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYHAEMOGLOBIN6739251216 May 202212:11125175g/L141
xyz01-00202 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYHAEMOGLOBIN6737530425 May 202216:38125175g/L132
xyz01-00202 Jun 1986D15MICRO - EXTRA BLOODSFAECAL HUMAN HAEMOGLOBINFAECAL HB.  SPEC 1.6737853730 May 202208:00   Not Detected 
xyz01-00202 Jun 1986SCRBIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6739251216 May 202212:11  umol/L6
xyz01-00202 Jun 1986SCRBIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6739251216 May 202212:113550g/L47
xyz01-00202 Jun 1986SCRBIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6739251216 May 202212:11>59 mL/min/1.73m2> 90
xyz01-00202 Jun 1986SCRBIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6739251216 May 202212:113550g/L47
xyz01-00302 Jun 1986D-1BIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6737530425 May 202216:38  umol/L7
xyz01-00302 Jun 1986D-1BIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6737530425 May 202216:383550g/L43
xyz01-00302 Jun 1986D-1BIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6737530425 May 202216:38>59 mL/min/1.73m2> 90
xyz01-00302 Jun 1986D-1BIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6737530425 May 202216:383550g/L43
xyz01-00302 Jun 1986D-1HAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6737530425 May 202216:3823.035.0s25.9
xyz01-00302 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6737530425 May 202216:38 <0.3x10{S9}/L< 0.1
xyz01-00302 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS6737530425 May 202216:38  %1
xyz01-00302 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYHAEMATOCRIT6737530425 May 202216:380.400.55 0.39
xyz01-00302 Jun 1986SCRHAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6739251216 May 202212:1123.035.0s27.1
xyz01-00302 Jun 1986SCRMICRO - EXTRA BLOODSFAECAL HUMAN HAEMOGLOBINFAECAL HB.  SPEC 1.6739477617 May 202215:30   Not Detected 
xyz01-00503 Apr 1993SCRBIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6737762419 May 202216:58>59 mL/min/1.73m285
xyz01-00503 Apr 1993SCRBIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6737762419 May 202216:583550g/L41
xyz01-00503 Apr 1993SCRHAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6737762419 May 202216:5823.035.0s25.9
xyz01-00503 Apr 1993SCRHAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6737762419 May 202216:58 <0.3x10{S9}/L0.0
xyz01-00703 Apr 1993SCRMICRO - EXTRA BLOODSFAECAL HUMAN HAEMOGLOBINFAECAL HB.  SPEC 1.6737037320 May 202214:51   Not Detected 
xyz01-00703 Apr 1993D-1BIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6737524025 May 202214:48  umol/L10
xyz01-00703 Apr 1993D-1BIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6737524025 May 202214:483550g/L37
xyz01-00703 Apr 1993D-1BIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6737524025 May 202214:48>59 mL/min/1.73m2> 90
xyz01-00703 Apr 1993D-1BIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6737524025 May 202214:483550g/L37
xyz01-00703 Apr 1993D 22HAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6737524025 May 202214:4823.035.0s28.4
xyz01-00703 Apr 1993D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6737524025 May 202214:48 <0.3x10{S9}/L0.0
xyz01-00703 Apr 1993D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS6737524025 May 202214:48  %0
xyz01-00703 Apr 1993UNSHAEMATOLOGYROUTINE HAEMATOLOGYHAEMATOCRIT6737524025 May 202214:480.400.55 0.38
xyz01-00703 Apr 1993UNSHAEMATOLOGYROUTINE HAEMATOLOGYHAEMOGLOBIN6737524025 May 202214:48125175g/L132
xyz01-00703 Apr 1993SCRBIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6737762419 May 202216:58  umol/L13
xyz01-00703 Apr 1993SCRBIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM67377624

19 May 2022

 

16:583550g/L41

 

i used the below statement to import data :

proc import file="path:xyz\folder\data.csv"
out=Vendor
dbms=dlm
replace;
getnames= yes;
guessingrows=MAX;
datarow = 2;
run;

 

but i getting the data values being split into different columns and not coming under same columns as in raw data.

 

can you please anyone help me to read the data exactly in the same columns as raw data above.

 

 

thanks!

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 11:56 AM (2548 views)  |   In reply to Sri_devi

There is no SAS 9.8 version.  To check the version of SAS that you are using check the first few lines of the SAS log.  Or if you are using some front end application to submit your SAS code (such as Enterprise Guide or SAS/Studio) so it is hard to see the actual SAS log then check the automatic macro variable SYSVLONG.

Example:

1   %put &=sysvlong;
SYSVLONG=9.04.01M5P091317

If you are having trouble viewing the text file with other tools you can always just use the SAS data step to look at the file. Use the LIST statement to dump the lines read to the SAS log.  You can use the OBS= option of the INFILE statement to limit the number of lines read.  (Or combine the FIRSTOBS= and OBS= option to read a range of lines in the middle of the file).

Example:

data _null_;
  infile 'myfile.csv' obs=10;
  input;
  list;
run;

View solution in original post

0 Likes
Reply
11 REPLIES 11
ballardw
Super User ballardw
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-02-2022 11:09 AM (2629 views)  |   In reply to Sri_devi

What you show is NOT CSV data in any of the normally accepted meanings.

If you have text, copy some of the text lines including the header from the file using a non-spreadsheet program such as Notepad. On the forum open a text box using the </> icon that appears above the message box and paste the text.

 

Make sure to include a couple of the lines that do not align as you expect.

 

Note: if you saved the file after opening with a spreadsheet program your data may have changed and not be the same as it was originally. Spreadsheets are obnoxious that way.

0 Likes
Reply
MayurJadhav
Quartz | Level 8 MayurJadhav
Quartz | Level 8

Re: impot CSv data file and read the data in correct column in sas

Posted 09-02-2022 11:17 AM (2624 views)  |   In reply to Sri_devi

You're missing delimiter= option in the proc import query.

I'm not sure what is the format of your input data but here are few examples to refer:

 

 

delimiter=' ';  /* for space-delimited data */
delimiter='09'x;  /* for tab-delimited data*/
delimiter='&';  /* for data separated by "&" */

 

Mayur Jadhav
BI Developer. Writer. Creative Educator.

SAS Blog → https://learnsascode.com
YouTube Channel: → https://www.youtube.com/@imayurj
0 Likes
Reply
Tom
Super User Tom
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-02-2022 11:19 AM (2622 views)  |   In reply to Sri_devi

You posted a table and not lines from a text file so we cannot tell what you file actually looks like.

You told PROC IMPORT that space was the delimiter in the file, but normally a CSV file uses comma as the delimiter.  Hence the name: Comma Separated Values.

 

You can use DBMS=CSV instead of DBMS=DLM

and/or add the DELIMITER statement to tell PROC IMPORT exactly which character to use a the delimiter (some places, especially in areas that use comma for the decimal point on numbers, use semicolon as the delimiter in files that they still call CSV files).

 

0 Likes
Reply
Tom
Super User Tom
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-02-2022 10:55 PM (2608 views)  |   In reply to Sri_devi

If I copy the last line of that table you posted in your question (and then convert the TAB characters into PIPE characters so they are VISIBILE to humans then I get this mess:

xyz|01-007|03 Apr 1993|SCR|BIOCHEMISTRY|GENERAL CHEMISTRY|ALBUMIN; SERUM|67377624|
19 May 2022

 

16:58|35|50|g/L|41

Someone has accidentally embedded END OF LINE characters into the MIDDLE of some of the values in your data.

To make the text file parsable you need to get rid of those end of line characters.  Since they do not appear to have added quotes around them (which is how EXCEL would write such gibberish) then it will be more difficult.

If you just have a handful of such messed up lines then just fix it using a text editor (such as the SAS program editor).

xyz|01-007|03 Apr 1993|SCR|BIOCHEMISTRY|GENERAL CHEMISTRY|ALBUMIN; SERUM|67377624|19 May 2022|16:58|35|50|g/L|41

Then PROC IMPORT should be able to handle it.

filename csv temp;
options parmcards=csv;
parmcards4;
Trail no|Subject id|Date of Birth|study day|Category name|Panel name|Description|Lab Acc number|Collection Date|Collection Time|Min value|Max value|Unit|Value
xyz|01-001|02 Jun 1986|SCR|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS (ABSOLUTE)|67392512|16 May 2022|12:11| |<0.3|x10{S9}/L|< 0.1
xyz|01-001|02 Jun 1986|SCR|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS|67392512|16 May 2022|12:11| | |%|1
xyz|01-001|02 Jun 1986|SCR|HAEMATOLOGY|ROUTINE HAEMATOLOGY|HAEMATOCRIT|67392512|16 May 2022|12:11|0.40|0.55| |0.42
xyz|01-001|02 Jun 1986|SCR|HAEMATOLOGY|ROUTINE HAEMATOLOGY|HAEMOGLOBIN|67392512|16 May 2022|12:11|125|175|g/L|141
xyz|01-002|02 Jun 1986|D-1|HAEMATOLOGY|ROUTINE HAEMATOLOGY|HAEMOGLOBIN|67375304|25 May 2022|16:38|125|175|g/L|132
xyz|01-002|02 Jun 1986|D15|MICRO - EXTRA BLOODS|FAECAL HUMAN HAEMOGLOBIN|FAECAL HB.  SPEC 1.|67378537|30 May 2022|08:00| | | |Not Detected 
xyz|01-002|02 Jun 1986|SCR|BIOCHEMISTRY|BILIRUBIN, TOTAL/DIRECT|BILIRUBIN; INDIRECT|67392512|16 May 2022|12:11| | |umol/L|6
xyz|01-002|02 Jun 1986|SCR|BIOCHEMISTRY|GENERAL CHEMISTRY|ALBUMIN; SERUM|67392512|16 May 2022|12:11|35|50|g/L|47
xyz|01-002|02 Jun 1986|SCR|BIOCHEMISTRY|GENERAL CHEMISTRY|EST. GLOM. FILT. RATE|67392512|16 May 2022|12:11|>59| |mL/min/1.73m2|> 90
xyz|01-002|02 Jun 1986|SCR|BIOCHEMISTRY|IONISED CALCIUM|ALBUMIN; SERUM|67392512|16 May 2022|12:11|35|50|g/L|47
xyz|01-003|02 Jun 1986|D-1|BIOCHEMISTRY|BILIRUBIN, TOTAL/DIRECT|BILIRUBIN; INDIRECT|67375304|25 May 2022|16:38| | |umol/L|7
xyz|01-003|02 Jun 1986|D-1|BIOCHEMISTRY|GENERAL CHEMISTRY|ALBUMIN; SERUM|67375304|25 May 2022|16:38|35|50|g/L|43
xyz|01-003|02 Jun 1986|D-1|BIOCHEMISTRY|GENERAL CHEMISTRY|EST. GLOM. FILT. RATE|67375304|25 May 2022|16:38|>59| |mL/min/1.73m2|> 90
xyz|01-003|02 Jun 1986|D-1|BIOCHEMISTRY|IONISED CALCIUM|ALBUMIN; SERUM|67375304|25 May 2022|16:38|35|50|g/L|43
xyz|01-003|02 Jun 1986|D-1|HAEMATOLOGY|COAGULATION PROFILE|APTT; PATIENT TIME|67375304|25 May 2022|16:38|23.0|35.0|s|25.9
xyz|01-003|02 Jun 1986|D-1|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS (ABSOLUTE)|67375304|25 May 2022|16:38| |<0.3|x10{S9}/L|< 0.1
xyz|01-003|02 Jun 1986|D-1|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS|67375304|25 May 2022|16:38| | |%|1
xyz|01-003|02 Jun 1986|D-1|HAEMATOLOGY|ROUTINE HAEMATOLOGY|HAEMATOCRIT|67375304|25 May 2022|16:38|0.40|0.55| |0.39
xyz|01-003|02 Jun 1986|SCR|HAEMATOLOGY|COAGULATION PROFILE|APTT; PATIENT TIME|67392512|16 May 2022|12:11|23.0|35.0|s|27.1
xyz|01-003|02 Jun 1986|SCR|MICRO - EXTRA BLOODS|FAECAL HUMAN HAEMOGLOBIN|FAECAL HB.  SPEC 1.|67394776|17 May 2022|15:30| | | |Not Detected 
xyz|01-005|03 Apr 1993|SCR|BIOCHEMISTRY|GENERAL CHEMISTRY|EST. GLOM. FILT. RATE|67377624|19 May 2022|16:58|>59| |mL/min/1.73m2|85
xyz|01-005|03 Apr 1993|SCR|BIOCHEMISTRY|IONISED CALCIUM|ALBUMIN; SERUM|67377624|19 May 2022|16:58|35|50|g/L|41
xyz|01-005|03 Apr 1993|SCR|HAEMATOLOGY|COAGULATION PROFILE|APTT; PATIENT TIME|67377624|19 May 2022|16:58|23.0|35.0|s|25.9
xyz|01-005|03 Apr 1993|SCR|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS (ABSOLUTE)|67377624|19 May 2022|16:58| |<0.3|x10{S9}/L|0.0
xyz|01-007|03 Apr 1993|SCR|MICRO - EXTRA BLOODS|FAECAL HUMAN HAEMOGLOBIN|FAECAL HB.  SPEC 1.|67370373|20 May 2022|14:51| | | |Not Detected 
xyz|01-007|03 Apr 1993|D-1|BIOCHEMISTRY|BILIRUBIN, TOTAL/DIRECT|BILIRUBIN; INDIRECT|67375240|25 May 2022|14:48| | |umol/L|10
xyz|01-007|03 Apr 1993|D-1|BIOCHEMISTRY|GENERAL CHEMISTRY|ALBUMIN; SERUM|67375240|25 May 2022|14:48|35|50|g/L|37
xyz|01-007|03 Apr 1993|D-1|BIOCHEMISTRY|GENERAL CHEMISTRY|EST. GLOM. FILT. RATE|67375240|25 May 2022|14:48|>59| |mL/min/1.73m2|> 90
xyz|01-007|03 Apr 1993|D-1|BIOCHEMISTRY|IONISED CALCIUM|ALBUMIN; SERUM|67375240|25 May 2022|14:48|35|50|g/L|37
xyz|01-007|03 Apr 1993|D 22|HAEMATOLOGY|COAGULATION PROFILE|APTT; PATIENT TIME|67375240|25 May 2022|14:48|23.0|35.0|s|28.4
xyz|01-007|03 Apr 1993|D-1|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS (ABSOLUTE)|67375240|25 May 2022|14:48| |<0.3|x10{S9}/L|0.0
xyz|01-007|03 Apr 1993|D-1|HAEMATOLOGY|ROUTINE HAEMATOLOGY|BASOPHILS|67375240|25 May 2022|14:48| | |%|0
xyz|01-007|03 Apr 1993|UNS|HAEMATOLOGY|ROUTINE HAEMATOLOGY|HAEMATOCRIT|67375240|25 May 2022|14:48|0.40|0.55| |0.38
xyz|01-007|03 Apr 1993|UNS|HAEMATOLOGY|ROUTINE HAEMATOLOGY|HAEMOGLOBIN|67375240|25 May 2022|14:48|125|175|g/L|132
xyz|01-007|03 Apr 1993|SCR|BIOCHEMISTRY|BILIRUBIN, TOTAL/DIRECT|BILIRUBIN; INDIRECT|67377624|19 May 2022|16:58| | |umol/L|13
xyz|01-007|03 Apr 1993|SCR|BIOCHEMISTRY|GENERAL CHEMISTRY|ALBUMIN; SERUM|67377624|19 May 2022|16:58|35|50|g/L|41
;;;;

proc import file=csv dbms=csv out=want replace;
  delimiter='|';
run;

501  proc import file=csv dbms=csv out=want replace;
502    delimiter='|';
503  run;

504   /**********************************************************************
505   *   PRODUCT:   SAS
506   *   VERSION:   9.4
507   *   CREATOR:   External File Interface
508   *   DATE:      01SEP22
509   *   DESC:      Generated SAS Datastep Code
510   *   TEMPLATE SOURCE:  (None Specified.)
511   ***********************************************************************/
512      data WORK.WANT    ;
513      %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
514      infile CSV delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;
515         informat Trail_no $3. ;
516         informat Subject_id $6. ;
517         informat Date_of_Birth DATE11. ;
518         informat study_day $3. ;
519         informat Category_name $20. ;
520         informat Panel_name $24. ;
521         informat Description $21. ;
522         informat Lab_Acc_number best32. ;
523         informat Collection_Date DATE11. ;
524         informat Collection_Time time20.3 ;
525         informat Min_value $4. ;
526         informat Max_value $4. ;
527         informat Unit $13. ;
528         informat Value $12. ;
529         format Trail_no $3. ;
530         format Subject_id $6. ;
531         format Date_of_Birth DATE11. ;
532         format study_day $3. ;
533         format Category_name $20. ;
534         format Panel_name $24. ;
535         format Description $21. ;
536         format Lab_Acc_number best12. ;
537         format Collection_Date DATE11. ;
538         format Collection_Time time20.3 ;
539         format Min_value $4. ;
540         format Max_value $4. ;
541         format Unit $13. ;
542         format Value $12. ;
543      input
544                  Trail_no  $
545                  Subject_id  $
546                  Date_of_Birth
547                  study_day  $
548                  Category_name  $
549                  Panel_name  $
550                  Description  $
551                  Lab_Acc_number
552                  Collection_Date
553                  Collection_Time
554                  Min_value  $
555                  Max_value  $
556                  Unit  $
557                  Value  $
558      ;
559      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
560      run;

NOTE: The infile CSV is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: 36 records were read from the infile (system-specific pathname).
      The minimum record length was 103.
      The maximum record length was 138.
NOTE: The data set WORK.WANT has 36 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


36 rows created in WORK.WANT from CSV.



NOTE: WORK.WANT data set was successfully created.
NOTE: The data set WORK.WANT has 36 observations and 14 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

 

 

0 Likes
Reply
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 12:32 AM (2580 views)  |   In reply to Tom

The below table raw data in given as Microsoft excel worksheet. I want to import in sas and work on reconciliation report. Please help me to import this table data into sas. As when i am doing using Proc import the data under specific columns as not getting imported correctly, it is spiting data values (Is it due to space between the data vales?) giving incorrect data under each columns.Please can anyone help to import his microsoft Excel worrksheet into SAS. other thin is I am using SAS 9.8 where it is not allowing me to import the excel hence i coverted the file to CSV file and

 

Trail noSubject idDate of Birthstudy dayCategory namePanel nameDescriptionLab Acc numberCollection DateCollection TimeMin valueMax valueUnitValue
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6739251216 May 20220.507639 <0.3x10{S9}/L< 0.1
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS6739251216 May 20220.507639  %1
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYHAEMATOCRIT6739251216 May 20220.50763901 0
xyz01-00102 Jun 1986SCRHAEMATOLOGYROUTINE HAEMATOLOGYHAEMOGLOBIN6739251216 May 20220.507639125175g/L141
xyz01-00202 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYHAEMOGLOBIN6737530425 May 20220.693056125175g/L132
xyz01-00202 Jun 1986D15MICRO - EXTRA BLOODSFAECAL HUMAN HAEMOGLOBINFAECAL HB.  SPEC 1.6737853730 May 20220.333333   Not Detected 
xyz01-00202 Jun 1986SCRBIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6739251216 May 20220.507639  umol/L6
xyz01-00202 Jun 1986SCRBIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6739251216 May 20220.50763935.050.0g/L47.0
xyz01-00202 Jun 1986SCRBIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6739251216 May 20220.507639>59 mL/min/1.73m2> 90
xyz01-00202 Jun 1986SCRBIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6739251216 May 20220.5076393550g/L47
xyz01-00302 Jun 1986D-1BIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6737530425 May 20220.693056  umol/L7.0
xyz01-00302 Jun 1986D-1BIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6737530425 May 20220.6930563550g/L43
xyz01-00302 Jun 1986D-1BIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6737530425 May 20220.693056>59 mL/min/1.73m2> 90
xyz01-00302 Jun 1986D-1BIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6737530425 May 20220.6930563550g/L43
xyz01-00302 Jun 1986D-1HAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6737530425 May 20220.6930562335s26
xyz01-00302 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6737530425 May 20220.693056 <0.3x10{S9}/L< 0.1
xyz01-00302 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS6737530425 May 20220.693056  %1
xyz01-00302 Jun 1986D-1HAEMATOLOGYROUTINE HAEMATOLOGYHAEMATOCRIT6737530425 May 20220.69305601 0
xyz01-00302 Jun 1986SCRHAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6739251216 May 20220.5076392335s27
xyz01-00302 Jun 1986SCRMICRO - EXTRA BLOODSFAECAL HUMAN HAEMOGLOBINFAECAL HB.  SPEC 1.6739477617 May 20220.645833   Not Detected 
xyz01-00503 Apr 1993SCRBIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6737762419 May 20220.706944>59 mL/min/1.73m285.00
xyz01-00503 Apr 1993SCRBIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6737762419 May 20220.70694435.0050.00g/L41.00
xyz01-00503 Apr 1993SCRHAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6737762419 May 20220.7069442335s26
xyz01-00503 Apr 1993SCRHAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6737762419 May 20220.706944 <0.3x10{S9}/L0.0
xyz01-00703 Apr 1993SCRMICRO - EXTRA BLOODSFAECAL HUMAN HAEMOGLOBINFAECAL HB.  SPEC 1.6737037320 May 20220.61875   Not Detected 
xyz01-00703 Apr 1993D-1BIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6737524025 May 20220.616667  umol/L10
xyz01-00703 Apr 1993D-1BIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6737524025 May 20220.6166673550g/L37
xyz01-00703 Apr 1993D-1BIOCHEMISTRYGENERAL CHEMISTRYEST. GLOM. FILT. RATE6737524025 May 20220.616667>59 mL/min/1.73m2> 90
xyz01-00703 Apr 1993D-1BIOCHEMISTRYIONISED CALCIUMALBUMIN; SERUM6737524025 May 20220.61666735.050.0g/L37.0
xyz01-00703 Apr 1993D 22HAEMATOLOGYCOAGULATION PROFILEAPTT; PATIENT TIME6737524025 May 20220.61666723.0035.00s28.40
xyz01-00703 Apr 1993D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS (ABSOLUTE)6737524025 May 20220.616667 <0.3x10{S9}/L0
xyz01-00703 Apr 1993D-1HAEMATOLOGYROUTINE HAEMATOLOGYBASOPHILS6737524025 May 20220.616667  %0.00
xyz01-00703 Apr 1993UNSHAEMATOLOGYROUTINE HAEMATOLOGYHAEMATOCRIT6737524025 May 20220.6166670.40.6 0.4
xyz01-00703 Apr 1993UNSHAEMATOLOGYROUTINE HAEMATOLOGYHAEMOGLOBIN6737524025 May 20220.616667125175g/L132
xyz01-00703 Apr 1993SCRBIOCHEMISTRYBILIRUBIN, TOTAL/DIRECTBILIRUBIN; INDIRECT6737762419 May 20220.706944  umol/L13
xyz01-00703 Apr 1993SCRBIOCHEMISTRYGENERAL CHEMISTRYALBUMIN; SERUM6737762419 May 20220.70694435.050.0g/L41.0
0 Likes
Reply
Tom
Super User Tom
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 02:04 PM (2542 views)  |   In reply to Sri_devi

Where is the CSV file? You posted another TABLE.  A CSV file is just text.  Something like:

Name,Sex,Age,Height,Weight
Henry,M,14,63.5,102.5
James,M,12,57.3,83
Jane,F,12,59.8,84.5
Janet,F,15,62.5,112.5

If you created the CSV file from Excel then the cells with embedded end of line characters should be quoted, so you can use the logic in this program to replace those characters so that SAS can read the right number of lines from the CSV file.

https://github.com/sasutils/macros/blob/master/replace_crlf.sas

 

0 Likes
Reply
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 12:42 AM (2578 views)  |   In reply to Tom
Hi, Can you please tell me how convert the TAB characters into PIPE characters?

also as you have mentioned " Someone has accidentally embedded END OF LINE characters into the MIDDLE of some of the values in your data.
To make the text file parsable you need to get rid of those end of line characters. Since they do not appear to have added quotes around them (which is how EXCEL would write such gibberish) then it will be more difficult.
If you just have a handful of such messed up lines then just fix it using a text editor (such as the SAS program editor).


How edit and fix this?

Just to update , The table of raw data in given as Microsoft excel worksheet. I want to import in sas and work on reconciliation report. Please help me to import this table data into sas. As when i am doing using Proc import the data under specific columns as not getting imported correctly, it is spiting data values (Is it due to space between the data vales?) giving incorrect data under each columns.Please can anyone help to import his microsoft Excel worrksheet into SAS. other thin is I am using SAS 9.8 where it is not allowing me to import the excel hence i coverted the file to CSV file and import data.

0 Likes
Reply
Tom
Super User Tom
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 10:46 AM (2556 views)  |   In reply to Sri_devi

Which format is the source file? 

Is it a text file, like the CSV file you mentioned in the original question.

 

Or is it an Excel file? If it is an Excel file which type is it? Is it the old XLS file format or the new XLSX file format.  You should not have any issues with spaces (or even end of line characters) when reading from an actual Excel file.  But you also have no control over what type of variable (or for character variables what length) will be created.  That will be determined by the contents of the columns in the Excel file.  If the column has any cell that is a character string (or all of the cells are empty) then SAS will create a character variable.  Only if all of the cells in the column have numbers will SAS create a numeric variable.

0 Likes
Reply
Tom
Super User Tom
Super User

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 11:56 AM (2550 views)  |   In reply to Sri_devi

There is no SAS 9.8 version.  To check the version of SAS that you are using check the first few lines of the SAS log.  Or if you are using some front end application to submit your SAS code (such as Enterprise Guide or SAS/Studio) so it is hard to see the actual SAS log then check the automatic macro variable SYSVLONG.

Example:

1   %put &=sysvlong;
SYSVLONG=9.04.01M5P091317

If you are having trouble viewing the text file with other tools you can always just use the SAS data step to look at the file. Use the LIST statement to dump the lines read to the SAS log.  You can use the OBS= option of the INFILE statement to limit the number of lines read.  (Or combine the FIRSTOBS= and OBS= option to read a range of lines in the middle of the file).

Example:

data _null_;
  infile 'myfile.csv' obs=10;
  input;
  list;
run;
0 Likes
Reply
Sri_devi
Obsidian | Level 7 Sri_devi
Obsidian | Level 7

Re: impot CSv data file and read the data in correct column in sas

Posted 09-04-2022 10:39 PM (2530 views)  |   In reply to Tom

Thank you will work around it.

 

0 Likes
Reply

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Watch Mastering the WHERE Clause in PROC SQL on YouTube

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • ‎09-02-2022 08:01 AM
  • 2659 views
  • 0 likes
  • 5 in conversation