BookmarkSubscribeRSS Feed
anilgvdbm
Quartz | Level 8

Dear Friends ,
I have 20 years income data of household level. for that i calculate Percentiles now i need to group like 1-2 percentiles are group 1 and 3-4 are group 2 like up to ten i need to group.
in SAS i need to write if and then codes for each year. so i dont have idea to use arrays. please help me in this.

I attached data for your information.

Table 1: Percentiles

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

3840

9795

17461

13203

16994

23436

25350

22863

27005

31948

23443

8679

14680

23152

20181

28419

34726

37602

32400

49392

58940

40204

16138

22036

28532

27956

34871

41041

55859

39890

65261

93045

54077

22020

27066

31435

35208

45183

48419

66204

51000

85441

112134

79532

27144

33238

40500

44160

57090

59760

90264

69334

97242

140537

96975

31633

43346

49200

54060

70867

72069

118168

87502

115363

164003

130210

40473

54767

58063

66744

80877

89957

135285

101120

129027

190376

156643

51504

65944

72027

81012

97269

109091

172723

135893

150455

236750

183306

65590

85163

102789

105525

138842

155322

214957

164344

200579

332079

231901

140170

218391

230555

266050

274477

255686

653227

379596

426260

700925

739109

Table 2 : Data of Household income

Inc2001

Inc2002

Inc2003

Inc2004

Inc2005

Inc2006

Inc2007

Inc2008

Inc2009

Inc2010

Inc2011

5400

17800

19890

23450

22500

34600

22300

22500

31000

33225

42710

45606

55940

57600

52000

68000

58000

28500

35990

48960

77800

32400

20000

138000

110000

108000

153950

154720

217800

321000

5000

6150

41000

5250

41543

10796

10300

19560

35600

21100

54030

56890

72050

85050

10000

12900

16278

14330

19781

35750

36660

37686

11000

9750

20000

13900

34340

25090

31500

21900

19200

10500

25000

13368

12683

27212

25000

2400

3000

8110

17500

23088

11999

131860

34964

27870

59600

30000

34733

29000

34540

71454

55850

96000

18400

29025

20000

16800

44675

28241

121434

36222

30000

21000

40000

15000

31875

134130

66100

99200

113743

136377

135627

185955

226647

215625

653227

307845

52645

81526

72977

125203

80726

178238

340600

310731

-12089

24526

19988

41058

61557

41878

90810

33150

-34462

218391

82326

218305

274477

125216

176254

136524

-16741

120024

125711

56525

70695

42883

201606

67920

131740

126789

123574

152760

98306

40268

35900

52540

37742

68970

47403

108878

99558

74990

65280

72989

29320

80828

65130

70405

139360

54768

38064

62976

71725

53039

120706

75985

328916

379596

53248

66300

63132

78740

92311

79050

120076

180491

35643

33368

48630

35979

61057

48284

211973

70253

2952

29822

21045

25632

94000

27418

14200

61437

26685

9726

8711

10770

26817

49703

26561

25541

35855

74815

67361

141430

138205

29744

56279

84483

102233

81505

63433

159983

268896

20054

47200

28545

49356

39913

163095

135580

118711

31030

43256

46516

68305

127033

175012

298783

147805

81850

88252

55149

70100

17853

41155

87110

57351

22747

26500

42673

43211

49303

55592

74806

100025

67312

164344

26018

23453

33415

45730

23176

49084

64722

90872

45285

72956

31520

95936

95713

167885

213580

125495

27232

57378

93079

44845

45227

33813

90758

27727

20113

35318

49243

112157

29118

30006

45718

27459

16100

13967

19208

19106

38845

53520

34100

51000

11694

9043

27750

38944

65969

56642

58870

144347

22000

14800

22500

20450

35523

26574

32820

43400

30862

20247

35050

25483

18960

53970

66220

62880

51299

39202

29212

74370

65478

91657

143817

159360

31892

44593

28873

32785

77549

65686

211455

69334

14900

18100

60000

62400

43990

45900

27551

12707

24450

30000

35374

33365

38015

57590

37460

23024

12918

23510

34189

39994

40439

38205

42314

27144

22437

31307

29983

39147

45170

52447

71418

20350

40370

53187

43475

63388

58690

67200

41853

4000

3000

5000

4250

7388

9330

10650

13020

36671

36184

47146

102187

120462

135893

24250

16500

47989

93250

24900

80900

32250

23550

32159

43005

14676

39114

106750

66075

71840

103599

218199

114786

52000

47700

57830

74915

93342

94775

52545

72173

108468

89115

38929

54890

39890

54275

63550

48250

14135

48881

49172

65337

82870

124896

113052

162220

112924

182763

157624

2383

28739

58239

82130

80887

80293

56801

133721

159206

155261

80824

21848

33495

27300

21205

48460

100280

156400

107642

146143

214941

137191

46384

47400

65385

44328

86262

153862

178162

43883

59782

12910

17040

21928

17750

66834

65924

58650

45125

56350

40965

89770

56156

59095

4741

38956

39101

44860

43245

54241

21559

33580

35209

48120

82634

142977

70990

50815

36970

55200

149503

112446

126710

89576

79439

155450

156981

12146

91104

38095

297183

150729

116632

12000

19750

17500

16660

19395

28447

40000

45100

104460

57470

53220

30400

24950

34798

47408

79525

84999

91350

84784

97748

35750

26964

89380

32170

27780

3600

3750

28500

5175

8728

8880

10000

15330

17170

12750

6850

36751

84820

61250

102864

159861

46477

81380

36385

75703

90742

96202

87407

132633

95985

67500

94450

60800

21140

33108

28577

15315

3020

23098

66139

22863

16313

96530

52376

48291

51631

81550

74683

87455

78242

119018

120424

119022

333448

212475

54131

102246

56700

80340

108199

110985

202025

160085

165351

331927

227106

5700

3600

10500

5190

3390

70525

66023

54890

81748

107482

99693

174582

101120

182760

546229

275093

37347

44603

39171

51076

80790

60215

115370

46166

84628

147171

88868

3800

-13265

29557

31892

25111

33404

62228

77828

99657

102266

119304

173866

28755

19515

24850

33866

64382

109411

121350

109292

309560

111499

91021

6000

5050

11711

11715

21038

31135

19583

28390

99851

152544

61786

32941

234181

281548

32250

34962

36426

50580

42900

25900

28750

21588

76383

40000

30175

38303

59700

99140

158440

22100

17100

24250

18643

27153

27960

33500

31190

50397

59308

43285

33310

43480

50776

96158

51270

60083

124363

93150

125433

135015

35260

22695

36004

30436

30223

154713

98355

359660

132567

198383

350541

138916

29063

23296

23315

20610

49422

51167

138613

125661

135327

257656

162806

6000

12000

8000

9625

20130

19010

13000

21940

40170

85960

22540

191060

112676

200538

166973

173419

201331

243089

18401

15103

11350

16485

23830

19720

19500

114498

115514

217689

128852

220341

281465

142705

70167

107373

213786

229640

149337

94032

127863

46227

12810

25500

18450

23380

43918

121950

158605

109260

86360

24286

27402

17114

28501

28340

35166

47832

41390

39350

38736

30550

8300

13300

11750

9300

9621

10123

11888

13056

23760

29950

23830

45005

30829

36461

32783

60036

62578

66603

44164

73441

107048

74288

71934

112557

72510

130500

182300

211000

242800

56251

51830

82078

48522

34638

59760

77118

69032

83611

250184

129127

140170

134528

167786

168610

120036

158398

293921

214051

226720

394032

219259

14066

34465

5000

2900

17750

22100

16450

16196

60059

73490

105347

84823

76643

142614

149791

95175

158047

297096

98847

65459

230555

266050

147445

61572

130242

294574

99309

194915

74363

138020

157729

228079

187175

120139

245111

250824

6000

2400

5000

3000

15204

54845

128298

77234

27357

135285

49600

25501

12634

45500

35643

81082

41729

96457

39847

85983

144709

166288

41015

28739

19985

45193

78132

62129

99740

53909

84563

180612

120414

23350

31356

47093

65264

16515

128937

120063

54371

123171

178675

152874

46395

43043

102609

73468

122811

150319

213312

87502

123967

199604

157062

63195

54120

104405

64786

70059

63449

117956

91641

141536

236075

207570

2906

32620

52577

94000

197556

251541

60820

107898

94860

75259

95609

255686

169934

97019

243613

176183

402386

47415

77222

58209

51934

93917

107565

143483

74107

122304

172936

111278

83909

49844

44174

110709

140759

130432

181711

144496

261847

590516

454397

47422

76923

139798

87751

216183

212816

387756

263975

426260

700925

739109

183126

42077

80492

55564

61584

78388

126327

232226

134540

2000

10800

8160

2000

17200

17100

37200

26300

38400

37000

44500

3900

36835

34385

34960

133899

34900

53653

14102

47042

44575

-198

6160

19926

15037

38346

112961

53604

123037

28593

15692

56534

6840

28537

9900

50779

92772

120164

113419

13200

9800

19500

7975

16246

29587

25690

34248

59651

67521

36931

39660

49200

65860

57100

59452

79125

81966

88149

77855

92384

185562

95194

178158

190246

243826

28504

49980

97740

64910

68578

68809

80125

107128

90110

102518

192023

110614

101750

300740

207930

34299

57764

44615

57313

93535

71652

119180

66983

141735

136364

65943

51812

60378

41707

41078

140767

87112

116953

54972

86318

421715

92064

31784

61821

46560

58937

71557

136211

155913

40353

76152

188431

205881

17420

36145

98968

32803

77058

154926

239451

137602

8932

61534

71790

79650

75188

85679

165205

147556

138004

222390

186736

4 REPLIES 4
ballardw
Super User

Can you give an example of what the output should look like?

anilgvdbm
Quartz | Level 8

Dear Ballardw,

My output looks like this

i need to do 5 groups using 10 percentiles like 1-2 is group 1  - - - 8-10 is group 5

Inc2001Income Group2001Inc2002Income Group2001
5400117800
22300322500
52000268000
200005138000
500046150
35600121100
10000212900
1100049750
19200110500
300028110
59600130000
18400 29025
30000 21000
113743 136377
52645 81526
-12089 24526
-34462 218391
-16741 120024

10000

12900

19200

10500

59600

30000

18400

29025

30000

21000

113743

136377

52645

81526

-12089

24526

-34462

218391

-16741

120024

Reeza
Super User

This looks more like a format problem to me than an array problem, though you could probably do it with temporary arrays.

How big is your data? Flipping the data  to a structure such as Year Income is probably easier to code, but could become too big.

How many years do you have and do you need to do this repeatedly or just a one  off?

xicheng_zhou_asu_edu
Calcite | Level 5

Here is my thoughts:

1. Create formats for all the years, name them like f2001pct, f2002pct, etc.

2. In data step setting 3 arrays

     array inc (20) inc2001 inc2002.....;

     array fmt (20) $ 10  _temporary_ ("f2001pct." "f2002pct." ..........); /* the . needs to be included in the quotation marks */

     array flag (20) group2001 group2002.........;

     do i = 1 to 20;

          flag(i)=putn(inc(i), fmt(i));

     end;

Hope this helps!

Mind that PUTN works run time, so it can be a bit slow. I used INPUTN in the same fashion on 8 million rows with another 5 variable array loop within this flag loop on Unix, it took a few hours to run.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1133 views
  • 0 likes
  • 4 in conversation