topic Re: Duplicate dataset, create a variable in SAS Programming

Duplicate dataset, create a variable

foxrol94 — Thu, 28 Oct 2021 13:49:55 GMT

Hi,

I have a dataset ONE and want to :

Duplicate ONE dataset
Each row is now doubled. So for each pair I will want to create a new variable top which is 'x' on the first row and nothing on the second.

The code below only gives me the character 'x' on the second line when I would like it on the first

data TMP_ONE;
	set ONE;
	top = 'x';
	run;

data TMP_1_ONE;
	set ONE;
	top = '';
	run;
data I_ONE;
set TMP_ONE TMP_1_ONE;
run;

proc sort data=I_ONE out=I_TWO;
by _all_  top;
run;

Thanks

Re: Duplicate dataset, create a variable

PaigeMiller — Thu, 28 Oct 2021 14:21:50 GMT

I think this does what you want, all in one DATA step.

data want;
    set ONE;
    top = 'x';
    output;
    top = ' ';
    output;
run;

Re: Duplicate dataset, create a variable

ballardw — Thu, 28 Oct 2021 21:39:10 GMT

@foxrol94 wrote:

Hi,

I have a dataset ONE and want to :

Duplicate ONE dataset

Each row is now doubled. So for each pair I will want to create a new variable top which is 'x' on the first row and nothing on the second.

The code below only gives me the character 'x' on the second line when I would like it on the first
data TMP_ONE;
	set ONE;
	top = 'x';
	run;

data TMP_1_ONE;
	set ONE;
	top = '';
	run;
data I_ONE;
set TMP_ONE TMP_1_ONE;
run;

proc sort data=I_ONE out=I_TWO;
by _all_  top;
run;
Thanks

Your sort says to sort by the order of TOP. Blank comes before 'X'. If you want the 'X' first, assuming no other value of Top is 'larger' or would come after X alphabetically then

proc sort data=I_ONE out=I_TWO;
   by _all_  descending top;
run;

Re: Duplicate dataset, create a variable

PaigeMiller — Fri, 29 Oct 2021 10:57:03 GMT

@ballardw this doesn't work for me.

data TMP_ONE;
	set sashelp.class;
	top = 'x';
	run;

data TMP_1_ONE;
	set  sashelp.class;
	top = ' ';
	run;
data I_ONE;
set TMP_ONE TMP_1_ONE;
run;

proc sort data=I_ONE out=I_TWO;
   by _all_  descending top;
run;

does not produce the requested ordering because (I think) the BY statement in PROC SORT is now really

by name sex age height weight top descending top;

so first TOP is sorted ascending and then within that TOP is sorted descending, and the first sort of TOP in ascending order "overpowers" the second sort of TOP in descending order.

Re: Duplicate dataset, create a variable

ballardw — Fri, 29 Oct 2021 14:35:27 GMT

@PaigeMiller wrote:

@ballardw this doesn't work for me.
data TMP_ONE;
	set sashelp.class;
	top = 'x';
	run;

data TMP_1_ONE;
	set  sashelp.class;
	top = ' ';
	run;
data I_ONE;
set TMP_ONE TMP_1_ONE;
run;

proc sort data=I_ONE out=I_TWO;
   by _all_  descending top;
run;
does not produce the requested ordering because (I think) the BY statement in PROC SORT is now really
by name sex age height weight top descending top;
so first TOP is sorted ascending and then within that TOP is sorted descending, and the first sort of TOP in ascending order "overpowers" the second sort of TOP in descending order.

Right. I never use _all_ for sort so forgot the variable Top would in effect be on the By twice.

But perhaps the OP can actually list the variables that are needed for the sort by name and have the variable in correct position in the BY statements.