From datalines
ABCDE 34436 23,566
2019Q1 100
2019Q3 299
2018Q1 188
2018Q3 299
MSLIFK 25250 405,405
2017Q1 102
2017Q3 244
2016Q1 135
2018Q3 243
WEFEF 23456 506,340
2016Q2 293
2017Q3 293
2017Q3 265
2018Q1 128
I only want to read the 3 Alphabet header lines, and first and third lines of detailed lines.
How should I program to read those certain detailed lines?
I somehow worked like this
data data1;
input A $1. ;
if A = 'A' or A='M' or A='W' then do;
if _n_^=1 then output;
input Name $5. Amount Amount2 Comma6.;
end;
else if A='2' then do;
input Year 1-4 @8 Spent;
After this I have no idea how to make it work
How to use only first and third row of detailed lines?
Ex) For ABCDE line, 2019Q1 100 line and 2018Q1 188 line
Thank you
yes, you can use line pointers in the INPUt statement. BTW, I assume you wnat one observation per line, as displayed by @KachiM :
data want;
infile datalines;
input hdrvar1 :$6. hdrvar2 hdrvar3 :comma7.0
#2 yyq1 $6. detail1
#4 yyq3 $6. detail3
#5;
datalines;
ABCDE 34436 23,566
2019Q1 100
2019Q3 299
2018Q1 188
2018Q3 299
MSLIFK 25250 405,405
2017Q1 102
2017Q3 244
2016Q1 135
2018Q3 243
WEFEF 23456 506,340
2016Q2 293
2017Q3 293
2017Q3 265
2018Q1 128
run;
The #2 and #4 tells sas to read the detail variables from the 2nd ad 4th lines. The trailing #5 tells it to go to the 5th line (but reads no varibles in). This is so that you will begin the next observation at "line 6".
If you show your code to read the whole input it will easy to show how to extract wanted rows.
Hi @skjhzzang
I have tried the code below, but I am not sure if this is what you want to get at the end. Let me know!
Best,
data data1 (keep=Name Amount Amount2) data2 (keep= Name spent);
infile datalines dlm=" ";
input Name $6. @;
if substr(Name,1,1) ne "2" then do;
input Amount Amount2 Comma10.;
output data1;
end;
else do;
input spent ;
output data2;
end;
datalines;
ABCDE 34436 23,566
2019Q1 100
2019Q3 299
2018Q1 188
2018Q3 299
MSLIFK 25250 405,405
2017Q1 102
2017Q3 244
2016Q1 135
2018Q3 243
WEFEF 23456 506,340
2016Q2 293
2017Q3 293
2017Q3 265
2018Q1 128
;
/* Duplicate rows to match data2 in a one to one reading*/
data data1bis;
set data1;
do i=1 to 2;
output;
end;
drop i;
run;
/* Select only 1st and 3rd rows */
data data2bis;
set data2;
rename Name = Year;
count+1;
if mod(count,2) then do;
output;
end;
drop count;
run;
/* Data merge*/
data want;
set data1bis;
set data2bis;
run;
Does every ID always have exactly 5 records, consisting of a header record followed by 4 detail records?
It is possible in one data step. Use / to skip input lines.
data have;
input ID $ num1 num2 comma10. /
YQ_1 $ Val_1 //
YQ_3 $ Val_3 / ;
datalines;
ABCDE 34436 23,566
2019Q1 100
2019Q3 299
2018Q1 188
2018Q3 299
MSLIFK 25250 405,405
2017Q1 102
2017Q3 244
2016Q1 135
2018Q3 243
WEFEF 23456 506,340
2016Q2 293
2017Q3 293
2017Q3 265
2018Q1 128
;
run;
Yes, that's what I want to see but, is it possible to use only one data set to make that?
yes, you can use line pointers in the INPUt statement. BTW, I assume you wnat one observation per line, as displayed by @KachiM :
data want;
infile datalines;
input hdrvar1 :$6. hdrvar2 hdrvar3 :comma7.0
#2 yyq1 $6. detail1
#4 yyq3 $6. detail3
#5;
datalines;
ABCDE 34436 23,566
2019Q1 100
2019Q3 299
2018Q1 188
2018Q3 299
MSLIFK 25250 405,405
2017Q1 102
2017Q3 244
2016Q1 135
2018Q3 243
WEFEF 23456 506,340
2016Q2 293
2017Q3 293
2017Q3 265
2018Q1 128
run;
The #2 and #4 tells sas to read the detail variables from the 2nd ad 4th lines. The trailing #5 tells it to go to the 5th line (but reads no varibles in). This is so that you will begin the next observation at "line 6".
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.