Hi draycut,
thanks for the code, worked perfectly!
If possible to expand the code, in my case I have more variables of interest. For example, the 4th line after the line that contains the word "stock" I have its industry:
data have2;
input n line $50.;
datalines;
30 doidn
31 fifeniofe
32 doewiij
33 fofugrugr
34 text jdjdjf stock IBM
35 djdff
36 25$
37 fg fgg
38 computers
39 text jdjdjf stock AAA
40 djdff
41 22$
42 fggh
43 food
44 text jdjdjf stock BBB
45 djdff
46 55$
47 duehuefhu
48 automotive
49 dfeoufeijer
50 ierhde43848
;
run;
Using your code I did the following:
data want2;
merge have2 have2(firstobs=3 rename=(line=price)) have2(firstobs=5 rename=(line=industry));
if find(line, "stock")>0 then output;
run;
And also got the result that I wanted!
I would like to ask for your opinion on the following:
1) In my data I have tens to hundreds of thousands of lines (most of them useless) and 5 to 7 variables that I need to extract, so this would mean merging 5 to 7 times the same data table just like above. Is this a good approach, i.e, could there be efficiency or some other problems (like the computer freezing for example)?
2) Recently I worked on some data where I also had to restructure it into a more analysis-friendly form using Excel VBA. Looking at my data I would perhaps do the following:
Loop through my original data until I find the word "stock". Once I find it, remember the cells row r and paste the cell's content into
a new spreadsheet in cell (1,2) (the first column in the new sheet is the stock, second column price etc).
In the original sheet the contents of the cell with row = r+2 are the stock's price and so they will be pasted into the new sheet's cell (2,2), and so on for the other variables...
And when I reach a new cell with the word "stock" I go to the next line in the new sheet.
It seems, at least to me, that using cells is easier conceptually (because I couldn't figure out how to do the above mentioned data restructuring using SAS), so is it possible to "cellulize" data in SAS? Is it even worth it?
Thanks
... View more