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 |
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
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;
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;
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
Thank you very much
brilliant code
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.
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
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.