topic Re: Selecting certain row observation in SAS Programming

Selecting certain row observation

skjhzzang — Sat, 23 Nov 2019 16:50:46 GMT

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

Re: Selecting certain row observation

Shmuel — Sat, 23 Nov 2019 15:56:58 GMT

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

Re: Selecting certain row observation

ed_sas_member — Sat, 23 Nov 2019 19:06:06 GMT

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;

Re: Selecting certain row observation

KachiM — Sun, 24 Nov 2019 03:47:10 GMT

@skjhzzang

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

Re: Selecting certain row observation

mkeintz — Sun, 24 Nov 2019 05:45:40 GMT

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

Re: Selecting certain row observation

skjhzzang — Sun, 24 Nov 2019 05:49:27 GMT

@KachiM

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

Re: Selecting certain row observation

skjhzzang — Sun, 24 Nov 2019 05:48:57 GMT

@mkeintz

yes, one header followed by 4 detailed records

Re: Selecting certain row observation

skjhzzang — Sun, 24 Nov 2019 05:50:05 GMT

@ed_sas_member

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

Re: Selecting certain row observation

mkeintz — Sun, 24 Nov 2019 05:51:07 GMT

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

Re: Selecting certain row observation

skjhzzang — Sun, 24 Nov 2019 06:12:33 GMT

@mkeintz

Thanks, this seems working

Re: Selecting certain row observation

KachiM — Sun, 24 Nov 2019 06:13:36 GMT

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