BookmarkSubscribeRSS Feed
Rahul_SAS
Quartz | Level 8

Hi Experts,

 

I am using below code but its not giving both by group value.

 

data list;
input Name$1-10 Class$ List_price ;
datalines;
Black Bora Track 796
Black Bora Road 399
Black Bora Track 1130
Black Bora Road 1995
Scirocco Mountain 899
Scirocco Mountain 459
Scirocco Road 2256
Scirocco Road 559
;
run;

proc sort data = list out=list1;
by Name Class;
run;

proc print data = list1;
run;

data final;
set list1;
by name class;
if first.name and first.class then List_price1=List_price;
else List_price1=0;
run;

 

Getting attached result. I simply need the List_price for both by group (name and class).

HAVE
Name Class List_Price
Black Bora  Track      796
Black Bora  Track 399
Black Bora  road 1130
Black Bora  Road       1995
Scirocco    Mountain   899
Scirocco    Mountain   459
Scirocco    Road      2256
Scirocco    Road       559

 

WANT
Name Class List_Price
Black Bora  Track      796
Black Bora  Track 0
Black Bora  road 1130
Black Bora  Road       0
Scirocco    Mountain   899
Scirocco    Mountain   0
Scirocco    Road      2256
Scirocco    Road       0

thanks

 

10 REPLIES 10
bobpep212
Quartz | Level 8

Try first.class only:

data final;
set list1;
by name class;
if first.class then List_price1=List_price;
else List_price1=0;
run;
Rahul_SAS
Quartz | Level 8

Hi bobpep212,

 

this doesn't solve the problem. Need all list prices for all Class within a Name like mentioned the WANT result.

bobpep212
Quartz | Level 8

I'm confused then. My output looks like your want below. It shows the list price for the first instance of that class for each Name group. All others list prices would be 0 as List_price1. (With the exception of the point Reeza makes about the road=1130, since 1130 is with the class 'Track' in your have dataset). It doesn't show all the list prices for all the class within a name because your program says not to do that with the else statement.

 

Rahul_SAS
Quartz | Level 8

Actually this code is not working for my original dev code.

Reeza
Super User

Please define what not working means. Is it giving you an error? 

 


@Rahul_SAS wrote:

Actually this code is not working for my original dev code.


 

novinosrin
Tourmaline | Level 20

assuming sets of 2

 

data HAVE;
input Name & $10.	Class $	List_Price;
cards;
Black Bora 	Track     	796
Black Bora 	Track	399
Black Bora 	Road	1130
Black Bora 	Road      	1995
Scirocco   	Mountain  	899
Scirocco   	Mountain  	459
Scirocco   	Road     	2256
Scirocco  	 Road      	559
;

data want;
set have;
by name class notsorted;
if last.class then list_price=0;
run;
Reeza
Super User

First, clean your data since road does not equal Road.

Then sort

Then use BY grroup to set to missing/0.

 

How is road 1130? See red section below. And with two prices which do you want to keep?

 

proc sort data=list;
by name class descending list_price;
run;

data want;
set list;
if not first.class then list_price=0;
run;


@Rahul_SAS wrote:

Hi Experts,

 

I am using below code but its not giving both by group value.

 

data list;
input Name$1-10 Class$ List_price ;
datalines;
Black Bora Track 796
Black Bora Road 399
Black Bora Track 1130
Black Bora Road 1995
Scirocco Mountain 899
Scirocco Mountain 459
Scirocco Road 2256
Scirocco Road 559
;
run;

proc sort data = list out=list1;
by Name Class;
run;

proc print data = list1;
run;

data final;
set list1;
by name class;
if first.name and first.class then List_price1=List_price;
else List_price1=0;
run;

 

Getting attached result. I simply need the List_price for both by group (name and class).

HAVE
Name Class List_Price
Black Bora  Track      796
Black Bora  Track 399
Black Bora  road 1130
Black Bora  Road       1995
Scirocco    Mountain   899
Scirocco    Mountain   459
Scirocco    Road      2256
Scirocco    Road       559

 

WANT
Name Class List_Price
Black Bora  Track      796
Black Bora  Track 0
Black Bora  road 1130
Black Bora  Road       0
Scirocco    Mountain   899
Scirocco    Mountain   0
Scirocco    Road      2256
Scirocco    Road       0

thanks

 


 

Rahul_SAS
Quartz | Level 8

lets keep it simple.

 

HAVE

Name Class List_Price
Black Bora  Track      796
Black Bora  Track 796
Black Bora  road 796
Black Bora  Road       796
Scirocco    Mountain   899
Scirocco    Mountain   899
Scirocco    Road      899
Scirocco    Road       899

 

WANT

Name Class List_Price
Black Bora  Track      796
Black Bora  Track 0
Black Bora  road 796
Black Bora  Road       0
Scirocco    Mountain   899
Scirocco    Mountain   0
Scirocco    Road      899
Scirocco    Road       0

 

Need value for first row of all class subgroup within name.

novinosrin
Tourmaline | Level 20

HI @Rahul_SAS Reeza was referring to perhaps the typo

in

Black Bora  road 796
Black Bora  Road       0

 

Also, it seems you apparently didn't test my code. May i request you to test plz

bobpep212
Quartz | Level 8
data have;
input Name$1-10 Class$ List_price ;
datalines;
Black Bora Track 796 
Black Bora Track 796 
Black Bora Road 796
Black Bora Road 796 
Scirocco  Mountain 899 
Scirocco  Mountain 899 
Scirocco  Road 899
Scirocco  Road 899 
;
run;
proc sort data = have;
by Name Class;
run;
data want;
set have;
by name class;
if first.class then List_price1=List_price;
else List_price1=0;
run;

This isn't exactly what you want?

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1380 views
  • 2 likes
  • 4 in conversation