topic Re: how to do the table? in SAS Programming

how to do the table?

xiangpang — Mon, 13 Aug 2018 02:01:39 GMT

Hello,

I have two questions here.

One is how to make the table like following 'want' from data try.

Another question is how to input the string with blank. For example, input the words 'the number/of/ those words ' instead of 'number/of/word'.

Thanks a lot

want
ID city word1          word2
1 steelcity    word/          assign/
   in/ values/
character/    to/
string another
6 greenhills word/          assign/
in/ values/
a/    to/
string    other
17 hills Searching/ number/
   Character/ of/
   String    word

One is how to make the table like 'want'.

data try;
length city word1 word2 $100.;
input id city$ word1$ word2$;

datalines;
1 steelcity word/in/character/string assign/values/to/another
6 greenhills word/in/a/string assign/values/to/other
17 hills Searching/Character/String number/of/word 
;
run;

want 
ID city        word1          word2
1  steelcity  word/          assign/
	in/			 values/
			  character/     to/
			  string         another
6  greenhills word/          assign/
			  in/		     values/
			  a/             to/
              string 	     other
17  hills     Searching/     number/
              Character/     of/
              String         word

Re: how to do the table?

ChrisNZ — Mon, 13 Aug 2018 05:46:22 GMT

Like this?

data HAVE;
  length CITY WORD1 WORD2  $100.;
  input ID CITY $ WORD1 & $ WORD2 & $;
datalines;
1 steelcity  word/in/character/string  assign/values/to/another
6 greenhills  word/in/a/string  assign/values/to/other
17 hills  Searching/Character/String  number/two words 
run;
data WANT;
  set HAVE;
  do I=1 to max(countw(WORD1,'/'), countw(WORD2,'/'));
    W1=scan(WORD1,I,'/');
    if scan(WORD1,I+1,'/') ne ' ' then W1=catt(W1,'/'); 
    W2=scan(WORD2,I,'/');
    if scan(WORD2,I+1,'/') ne ' ' then W2=catt(W2,'/');
    output;
    call missing(ID,CITY);
  end;
  keep ID CITY W1 W2;
  rename W1=WORD1 W2=WORD2;
run;

CITY	ID	WORD1	WORD2
steelcity	1	word/	assign/
	.	in/	values/
	.	character/	to/
	.	string	another
greenhills	6	word/	assign/
	.	in/	values/
	.	a/	to/
	.	string	other
hills	17	Searching/	number/
	.	Character/	two words
	.	String

Re: how to do the table?

Kurt_Bremser — Mon, 13 Aug 2018 05:50:44 GMT

Are the numbers of words always the same for a given observation in try?

That would enable using a for loop, otherwise you'd need a do while:

data want (keep=id word1 word2);
set try (rename=(word1=_word1 word2=_word2));
i = 1;
word1 = scan(_word1,1,'|');
word2 = scan(_word2,1,'|');
do while (word1 ne '' or word2 ne '');
  output;
  i + 1;
  word1 = scan(_word1,i,'|');
  word2 = scan(_word2,i,'|');
end;
run;

Not tested, as I'm on my tablet.

Re: how to do the table?

shahparth260 — Mon, 13 Aug 2018 05:52:49 GMT

For the Second kind of question, I suggest you Proc Format which is easy to use.

Proc format;

value $ change ( this is your choice of name)

"number/of/word"="the number/of/ those words"

;

run;

For the first question, I am trying but not getting your question somehow, if you can explain I would like to help you out.

I think you can use proc report for this kind of scenario but before that, you need to rearrange data.