BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kaushik1
Obsidian | Level 7

Following type of data I am having for processing...

data sample;
input index sales product_id $;
datalines;
1 10 100_10
1 20 10_100
1 30 1_50
1 15 12_500
2 12 100_10
2 25 10_100
2 25 1_50
2 10 12_500
;

After passing this data to PROC TRANSPOSE

proc transpose data=sample out=SampleT;
var sales;
id product_id;
by index;
run;

I am getting product_id as '_100_10' in SampleT i don't want first "_" sign to be there in data. 

Obs	index  _NAME_  _100_10  _10_100  _1_50  _12_500
 1	  1	  sales	    10	      20     30	     15
 2	  2	  sales	    12	      25     25	     10

What should I do...?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@Kaushik1 wrote:

Why this problem is coming, when I am trying to execute in SAS University Edition and SAS 9.4(desktop edition) but not in SAS Studio 3.8?


That is most probably because the Workspace Server behind SAS Studio is configured to run with

options validvarname=v7;

instead of validvarname=any. Which is a very good idea IMO, as it prevents you from having silly column names that need the "name literal" construct every time you use them.

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

This happens because a SAS variable name can not begin with a number..

 

So the question is rather: How do you want to variable names shaped?

Kaushik1
Obsidian | Level 7

Why this problem is coming, when I am trying to execute in SAS University Edition and SAS 9.4(desktop edition) but not in SAS Studio 3.8?

Kurt_Bremser
Super User

@Kaushik1 wrote:

Why this problem is coming, when I am trying to execute in SAS University Edition and SAS 9.4(desktop edition) but not in SAS Studio 3.8?


That is most probably because the Workspace Server behind SAS Studio is configured to run with

options validvarname=v7;

instead of validvarname=any. Which is a very good idea IMO, as it prevents you from having silly column names that need the "name literal" construct every time you use them.

Jagadishkatam
Amethyst | Level 16

Please try

 

data sample;
input index sales product_id $;
product_id=compress(product_id,'_');
datalines;
1 10 100_10
1 20 10_100
1 30 1_50
1 15 12_500
2 12 100_10
2 25 10_100
2 25 1_50
2 10 12_500
;

proc transpose data=sample out=SampleT prefix=col;
var sales;
id product_id;
by index;
run;
Thanks,
Jag
maggiem_sas
SAS Employee

I am studying PROC TRANSPOSE right now for the certification exam, so this is of interest to me!

 

The COMPRESS function doesn't work for me as it is shown in the solution, because it removes the underscore from the input data set before the transpose. You would have to use it on all the variables in the output data set.

 

I think the correct solution was this from @Kurt_Bremser  which allows variables to start with a number:

options validvarname=any;

Or you could add the PREFIX= option to PROC TRANSPOSE like this:

proc transpose data=sample out=SampleT prefix=n;
var sales;
id product_id;
by index;
run;

 

Kaushik1
Obsidian | Level 7

Option validvarname=any;

works.

problem with second solution is, for different input data it will prefix 'n' which is not preferable.

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
  • 6 replies
  • 640 views
  • 4 likes
  • 5 in conversation