Selecting certain row observation

[skjhzzang]

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

[Shmuel]

If you show your code to read the whole input it will easy to show how to extract wanted rows.

[ed_sas_member]

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;

[mkeintz]

Does every ID always have exactly 5 records, consisting of a header record followed by 4 detail records?

[skjhzzang]

@mkeintz 

yes, one header followed by 4 detailed records

[KachiM]

@skjhzzang 

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;

[skjhzzang]

@ed_sas_member 

Is it possible to use only one data set to make it?

[KachiM]

@skjhzzang 

I am not sure what output you want. Do you want an output like this?

[skjhzzang]

@KachiM 

Yes, that's what I want to see but, is it possible to use only one data set to make that?

[mkeintz · Accepted solution]

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

[skjhzzang]

@mkeintz 

Thanks, this seems working