topic Reading data in New SAS User

Reading data

BrahmanandaRao — Sat, 20 Jul 2019 08:06:29 GMT




data dsn;
input empname$ 20. empcode ;
cards;
Oliver21070
Jack21071
Harry21072
Jacob20051
Charlie20008
George20058
William20010
;
run;

I want output below format


EMPNAME	EMPCODE
Oliver	21070
Jack2	21071
Harry	21072
Jacob	20051
Charlie	20008
George	20058
William	20010

Re: Reading data

KachiM — Sat, 20 Jul 2019 08:56:58 GMT

Values are strings. The Input statement cann't determine the delimiting position from string to number. Here is solution which reads the values as "STR" and splits it into empname and empcode:

data dsn;
input str $20.;
cards;
Oliver21070
Jack21071
Harry21072
Jacob20051
Charlie20008
George20058
William20010
;
run;

data want;
   set dsn;
   length empname $20;
   empname=substr(str,1,anydigit(str)-1);
   empcode = compress(str,,'kd');
drop str;
run;

Re: Reading data

Patrick — Sat, 20 Jul 2019 09:23:22 GMT

Assuming the last 5 positions in the source data should get mapped into variable Empcode below syntax should work as well.

data sample(drop=_:);
  infile datalines truncover;
  input @;
  _fwidth=lengthn(_infile_)-5;
  input empname $varying20. _fwidth empcode $5.;
  datalines;
Oliver21070
Jack21071
Harry21072
Jacob20051
Charlie20008
George20058
William20010
;
run;

Re: Reading data

novinosrin — Sat, 20 Jul 2019 15:54:00 GMT

data dsn;
input ;
empname=compress(_infile_,,'d');
empcode=compress(_infile_,,'kd');
cards;
Oliver21070
Jack21071
Harry21072
Jacob20051
Charlie20008
George20058
William20010
;
run;

Compress would default to the length of the first argument.. You could use a length statement to assign an appropriate length

Re: Reading data

Tom — Sat, 20 Jul 2019 15:59:45 GMT

Can you fix the data so that there is some delimiter between the two fields? Or so that the second field starts in a specific column?

Your program is assuming that EMPCODE starts in column 21. Also EMPCODE is probably not a number. There is no value in taking the mean of EMPCODE.

So if you put the data into fixed positions the code could look like this:

data dsn;
  input empname $20. empcode $5.;
cards;
Oliver              21070
Jack                21071
Harry               21072
Jacob               20051
Charlie             20008
George              20058
William             20010
;

Or you could put a delimiter between the two fields and read it using LIST MDOE instead of FORMATTED input.

data dsn;
  infile cards dsd dlm='|' truncover ;
  length empname $20 empcode $5;
  input empname empcode ;
cards;
Oliver  |21070
Jack    |21071
Harry   |21072
Jacob   |20051
Charlie |20008
George  |20058
William |20010
;

Note: do not leave a space in the middle of your informat. In this line:

input empname$ 20. empcode ;

The $ is telling SAS to define EMPNAME as a character variable. Then when it sees the INFORMAT of 20. it will recognize that it is the wrong type of informat for a character variable. So SAS will silently convert by adding $ in front for you.

Re: Reading data

BrahmanandaRao — Sat, 20 Jul 2019 16:06:53 GMT

Thank you very much

brilliant code

Re: Reading data

BrahmanandaRao — Sat, 20 Jul 2019 16:11:41 GMT

Hi Tom

Thank you for your support

here we have small data to set pipe symbol in between two variables

what if very huge data can we edit manually

Re: Reading data

Tom — Sat, 20 Jul 2019 16:34:40 GMT

@BrahmanandaRao wrote:

Hi Tom

Thank you for your support

here we have small data to set pipe symbol in between two variables

what if very huge data can we edit manually

First step is to send the data back to who ever created it and ask that it be created in a readable format.

If that fails then you will need to use one of the methods posted to attempt the tease the two field apart. You will probably want to do some QC on the result to make sure that the algorithm worked for all cases.