topic Re: impot CSv data file and read the data in correct column in sas in New SAS User

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

Sri_devi — Fri, 02 Sep 2022 12:01:07 GMT

Hello,

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

i have raw data as below :

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

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

xyz

01-002

02 Jun 1986

SCR

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67392512

16 May 2022

12:11

g/L

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

g/L

xyz

01-003

02 Jun 1986

D-1

BIOCHEMISTRY

BILIRUBIN, TOTAL/DIRECT

BILIRUBIN; INDIRECT

67375304

25 May 2022

16:38

umol/L

xyz

01-003

02 Jun 1986

D-1

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67375304

25 May 2022

16:38

g/L

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

g/L

xyz

01-003

02 Jun 1986

D-1

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67375304

25 May 2022

16:38

23.0

35.0

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

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

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

xyz

01-005

03 Apr 1993

SCR

BIOCHEMISTRY

IONISED CALCIUM

ALBUMIN; SERUM

67377624

19 May 2022

16:58

g/L

xyz

01-005

03 Apr 1993

SCR

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67377624

19 May 2022

16:58

23.0

35.0

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

xyz

01-007

03 Apr 1993

D-1

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67375240

25 May 2022

14:48

g/L

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

g/L

xyz

01-007

03 Apr 1993

D 22

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67375240

25 May 2022

14:48

23.0

35.0

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

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

xyz

01-007

03 Apr 1993

SCR

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67377624

19 May 2022

16:58

g/L

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!

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

ballardw — Fri, 02 Sep 2022 15:09:28 GMT

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.

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

MayurJadhav — Fri, 02 Sep 2022 15:17:49 GMT

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 "&" */

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

Tom — Fri, 02 Sep 2022 15:19:55 GMT

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).

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

Tom — Sat, 03 Sep 2022 02:57:36 GMT

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

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

Sri_devi — Sun, 04 Sep 2022 04:32:40 GMT

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 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

0.507639

<0.3

x10{S9}/L

< 0.1

xyz

01-001

02 Jun 1986

SCR

HAEMATOLOGY

ROUTINE HAEMATOLOGY

BASOPHILS

67392512

16 May 2022

0.507639

xyz

01-001

02 Jun 1986

SCR

HAEMATOLOGY

ROUTINE HAEMATOLOGY

HAEMATOCRIT

67392512

16 May 2022

0.507639

xyz

01-001

02 Jun 1986

SCR

HAEMATOLOGY

ROUTINE HAEMATOLOGY

HAEMOGLOBIN

67392512

16 May 2022

0.507639

125

175

g/L

141

xyz

01-002

02 Jun 1986

D-1

HAEMATOLOGY

ROUTINE HAEMATOLOGY

HAEMOGLOBIN

67375304

25 May 2022

0.693056

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

0.333333

Not Detected

xyz

01-002

02 Jun 1986

SCR

BIOCHEMISTRY

BILIRUBIN, TOTAL/DIRECT

BILIRUBIN; INDIRECT

67392512

16 May 2022

0.507639

umol/L

xyz

01-002

02 Jun 1986

SCR

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67392512

16 May 2022

0.507639

35.0

50.0

g/L

47.0

xyz

01-002

02 Jun 1986

SCR

BIOCHEMISTRY

GENERAL CHEMISTRY

EST. GLOM. FILT. RATE

67392512

16 May 2022

0.507639

>59

mL/min/1.73m2

> 90

xyz

01-002

02 Jun 1986

SCR

BIOCHEMISTRY

IONISED CALCIUM

ALBUMIN; SERUM

67392512

16 May 2022

0.507639

g/L

xyz

01-003

02 Jun 1986

D-1

BIOCHEMISTRY

BILIRUBIN, TOTAL/DIRECT

BILIRUBIN; INDIRECT

67375304

25 May 2022

0.693056

umol/L

7.0

xyz

01-003

02 Jun 1986

D-1

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67375304

25 May 2022

0.693056

g/L

xyz

01-003

02 Jun 1986

D-1

BIOCHEMISTRY

GENERAL CHEMISTRY

EST. GLOM. FILT. RATE

67375304

25 May 2022

0.693056

>59

mL/min/1.73m2

> 90

xyz

01-003

02 Jun 1986

D-1

BIOCHEMISTRY

IONISED CALCIUM

ALBUMIN; SERUM

67375304

25 May 2022

0.693056

g/L

xyz

01-003

02 Jun 1986

D-1

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67375304

25 May 2022

0.693056

xyz

01-003

02 Jun 1986

D-1

HAEMATOLOGY

ROUTINE HAEMATOLOGY

BASOPHILS (ABSOLUTE)

67375304

25 May 2022

0.693056

<0.3

x10{S9}/L

< 0.1

xyz

01-003

02 Jun 1986

D-1

HAEMATOLOGY

ROUTINE HAEMATOLOGY

BASOPHILS

67375304

25 May 2022

0.693056

xyz

01-003

02 Jun 1986

D-1

HAEMATOLOGY

ROUTINE HAEMATOLOGY

HAEMATOCRIT

67375304

25 May 2022

0.693056

xyz

01-003

02 Jun 1986

SCR

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67392512

16 May 2022

0.507639

xyz

01-003

02 Jun 1986

SCR

MICRO - EXTRA BLOODS

FAECAL HUMAN HAEMOGLOBIN

FAECAL HB. SPEC 1.

67394776

17 May 2022

0.645833

Not Detected

xyz

01-005

03 Apr 1993

SCR

BIOCHEMISTRY

GENERAL CHEMISTRY

EST. GLOM. FILT. RATE

67377624

19 May 2022

0.706944

>59

mL/min/1.73m2

85.00

xyz

01-005

03 Apr 1993

SCR

BIOCHEMISTRY

IONISED CALCIUM

ALBUMIN; SERUM

67377624

19 May 2022

0.706944

35.00

50.00

g/L

41.00

xyz

01-005

03 Apr 1993

SCR

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67377624

19 May 2022

0.706944

xyz

01-005

03 Apr 1993

SCR

HAEMATOLOGY

ROUTINE HAEMATOLOGY

BASOPHILS (ABSOLUTE)

67377624

19 May 2022

0.706944

<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

0.61875

Not Detected

xyz

01-007

03 Apr 1993

D-1

BIOCHEMISTRY

BILIRUBIN, TOTAL/DIRECT

BILIRUBIN; INDIRECT

67375240

25 May 2022

0.616667

umol/L

xyz

01-007

03 Apr 1993

D-1

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67375240

25 May 2022

0.616667

g/L

xyz

01-007

03 Apr 1993

D-1

BIOCHEMISTRY

GENERAL CHEMISTRY

EST. GLOM. FILT. RATE

67375240

25 May 2022

0.616667

>59

mL/min/1.73m2

> 90

xyz

01-007

03 Apr 1993

D-1

BIOCHEMISTRY

IONISED CALCIUM

ALBUMIN; SERUM

67375240

25 May 2022

0.616667

35.0

50.0

g/L

37.0

xyz

01-007

03 Apr 1993

D 22

HAEMATOLOGY

COAGULATION PROFILE

APTT; PATIENT TIME

67375240

25 May 2022

0.616667

23.00

35.00

28.40

xyz

01-007

03 Apr 1993

D-1

HAEMATOLOGY

ROUTINE HAEMATOLOGY

BASOPHILS (ABSOLUTE)

67375240

25 May 2022

0.616667

<0.3

x10{S9}/L

xyz

01-007

03 Apr 1993

D-1

HAEMATOLOGY

ROUTINE HAEMATOLOGY

BASOPHILS

67375240

25 May 2022

0.616667

0.00

xyz

01-007

03 Apr 1993

UNS

HAEMATOLOGY

ROUTINE HAEMATOLOGY

HAEMATOCRIT

67375240

25 May 2022

0.616667

0.4

0.6

0.4

xyz

01-007

03 Apr 1993

UNS

HAEMATOLOGY

ROUTINE HAEMATOLOGY

HAEMOGLOBIN

67375240

25 May 2022

0.616667

125

175

g/L

132

xyz

01-007

03 Apr 1993

SCR

BIOCHEMISTRY

BILIRUBIN, TOTAL/DIRECT

BILIRUBIN; INDIRECT

67377624

19 May 2022

0.706944

umol/L

xyz

01-007

03 Apr 1993

SCR

BIOCHEMISTRY

GENERAL CHEMISTRY

ALBUMIN; SERUM

67377624

19 May 2022

0.706944

35.0

50.0

g/L

41.0

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

Sri_devi — Sun, 04 Sep 2022 04:42:49 GMT

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.

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

Kurt_Bremser — Sun, 04 Sep 2022 07:58:22 GMT

Open the resulting csv file with a text editor (Notepad or Notepad++), and copy/paste some of the offending lines ihto a window opened with this button:

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

Tom — Sun, 04 Sep 2022 15:00:24 GMT

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.

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

Tom — Sun, 04 Sep 2022 15:56:49 GMT

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;

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

Tom — Sun, 04 Sep 2022 18:04:58 GMT

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

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

Sri_devi — Mon, 05 Sep 2022 02:39:40 GMT

Thank you will work around it.